CODEWITHSUNDEEP
excel
user33484
user33484

Reputation: 568

Excel - using lookup functions with more than one lookup value

I have a document which has a list of agency numbers.

I know commpany X has 4 different agency numbers, for example: 111,222,333,444. I want to search the document and if it hits a match of 111, 222,333 or 444 I want it to return "company X" - I can do this using IF statements but there are many companies and I want a more efficient way of doing it

example of if statement:

=IF(OR(K2=111,K2=222,K2=333,K2=444),"company X", "other")

Upvotes: 0

Views: 51

Answers (1)

frIT
frIT

Reputation: 3285

I suggest you should use the VLOOKUP function (or one of the other LOOKUP functions). To make it work, you would need a separate table that lists all your companies associated with their numbers, e.g.

    [A]    [B]
[1] 1111   Company X
[2] 2222   Company X
[3] 3333   Company X
[4] 4444   Company X
[5] 5555   Company AA
[6] 6666   Company BBB
(etc)

I assume for the example this is on Sheet2. Your lookup statement would look something like this:

=VLOOKUP(K2, Sheet2!A:B, 2, FALSE)

The help on the function wizard (or the documentation) will explain the arguments in more detail and help you fine-tune them to your particular circumstances.

Upvotes: 1

Related Questions