How to look at a cell's text and assign different values depending on what the text is-CodePudding

This is a classic IF ELSE statement problem but I deciding to just use a LOOKUP so it's easier to understand:

I want to look at the value of cell D1230 and have the following instructions:

If text is either: "CE BSW", "Datalinks", "CE SAC, Infra"  -> Assign "1"
If text is either: "PFT","CFT"  -> Assign "2"
If text is either: "AHCC","SIC (Spark Igntion Controls)","Fuel Systems" –> Assign "3"

This is my formula:

=LOOKUP(D1230,{"CE BSW";"Datalinks";"CE SAC, Infra";"PFT";"CFT";"AHCC";"SIC"},{"1";"1";"1";"2";"2";"3";"3"})

Unfortunately it's not working and I'm getting a #N/A error, any help would be greatly appreciated.

CodePudding user response：

Use IFS:

=IFS(
    OR(D1230={"CE BSW", "Datalinks", "CE SAC, Infra"}),1,
    OR(D1230={"PFT","CFT"}),2,
    OR(D1230={"AHCC","SIC (Spark Igntion Controls)","Fuel Systems"}),3,
    TRUE,"")

Another method:

=INDEX({1;1;1;2;2;3;3},
    MATCH(D1230,{"CE BSW";"Datalinks";"CE SAC, Infra";"PFT";"CFT";"AHCC";"SIC"},0))

The difference between this and your LOOKUP is that LOOKUP requires the lookup range be sorted ascending. MATCH with 0 as the third criterion will look for an exact match and does not require them to be sorted.