CODEWITHSUNDEEP
excelexcel-formulanestednested-if
Shan Kashyap
Shan Kashyap

Reputation: 1

Excel - multiple IF conditions

I am trying to write a formula or function which looks for certain key words in one column and based on keywords, assign owners in another column.

Something like

If G4 contains switch or router or dir or cable then owner is Mike, If G4 contains server or chassis then owner is Mabel, If G4 contains bike or car or van then owner is Martha, else owner is Melvin

I tried this but no it keeps resulting Mike for cells, I tried putting OR as multiple FIND but it kept erroring

=IF(ISERROR(FIND("switch",G4)), IF(ISERROR(FIND("Server",G4)), "Mike", "Mabel"), "Melvin)"

Tried ISERROR and FIND, see original comment.

Upvotes: 0

Views: 58

Answers (3)

nkalvi
nkalvi

Reputation: 2614

Thanks for the fun 'assignment' :)

XLOOKUP with wildcard

This short version may be sufficient:

=XLOOKUP("*" & G2:G6 & "*", B3:B5, A3:A5, A2, 2)

XLOOKUP result

MMULT for fun

It may be easier if you create a small table with owner and items details.

  • Enter items to assign in $G$2:$G$6, expand the range as needed
  • MMULT is used to get the index for the matching row
  • XLOOKUP returns the owner for the first match
  • You can change the last line (VSTACK) to other variables to see the result of the variables or steps.
=LET(
    default_owner, $A$2,
    item_col, $B$3:INDEX($B$3:$B$100, COUNTA($B$3:$B$100)),
    owner_col, $A$3:INDEX($A$3:$A$100, COUNTA($A$3:$A$100)),
    items_to_assign, $G$2:$G$6,
    delim, ",",
    cols_per_row, LEN(item_col) - LEN(SUBSTITUTE(item_col, delim, )) +
        1,
    col_indices, SEQUENCE(, MAX(cols_per_row)),
    items_expanded, IFNA(
        TRIM(
            TEXTBEFORE(
                TEXTAFTER(
                    delim & item_col & delim,
                    delim,
                    col_indices
                ),
                delim
            )
        ),
        ""
    ),
    assign_item, LAMBDA(item,
        LET(
            item_matched, MMULT(
                --(items_expanded = TRIM(item)),
                SEQUENCE(COLUMNS(items_expanded), , 1, 0)
            ),
            XLOOKUP(1, item_matched, owner_col, default_owner)
        )
    ),
    VSTACK("Owner", MAP(items_to_assign, assign_item))
)

Formula and result

Upvotes: 0

Michal
Michal

Reputation: 5848

You should try SWITCH instead:

=SWITCH(TRUE(),
cell=condition,result,
cell=condition2,result2, etc)

or

=SWITCH(
cell_address,
condition,result,
condition2,result2,
etc..)

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Hard to know without seeing data samples with expected output. But frequently, complicated IF statements can be simplified by using tables.

Here is a formula that does what you describe, and the tables are hard-coded in the formula, but you can easily set up tables on your worksheet.

I used SEARCH instead of FIND for case-insensitivity.

=IFERROR(
    INDEX(
        {
            "Mike";
            "Mike";
            "Mike";
            "Mike";
            "Mabel";
            "Mabel";
            "Martha";
            "Martha";
            "Martha"
        },
        XMATCH(
            TRUE,
            ISNUMBER(
                SEARCH(
                    {
                        "switch";
                        "router";
                        "dir";
                        "cable";
                        "server";
                        "chassis";
                        "bike";
                        "car";
                        "van"
                    },
                    G4
                )
            )
        )
    ),
    "Melvin"
)

Upvotes: 2

Related Questions