Excel replace with advanced if (multiple conditions)-CodePudding

I have a column in Excel with over 10,000 cells of data. They have the following four different data:

PC is online(connected)
PC is down(notconnected)
PC is down(disabled)
PC is down(other-disabled)

From this, I wish to create a formula so that I can transform this data to the following:

Connected
Not Connected
Disabled
Disabled

How to do this so I can do it in one column?

CodePudding user response：

You need to define a lookup table for that, and put on cell F2 the following formula:

=XLOOKUP(A2:A10,C2:C5,D2:D5, "CASE NOT DEFINED")

and here is the output:

For safety purpose added as a last resort the scenario: CASE NOT DEFINED to identify any values not defined.

If your excel version doesn't support XLOOKUP then you can use the following instead:

=IFERROR(VLOOKUP(A2:A10,C2:D5, 2,0), "CASE NOT DEFINED")