CODEWITHSUNDEEP
excelvlookup
Sydney
Sydney

Reputation: 12212

VLOOKUP search on several columns

I have an Excel spreadsheet with 3 columns. I would like to lookup for a value that can be in the first 2 and then get the corresponding value from the third one.

A B C
Mustang Empty Ford
Camaro Corvette Chevrolet

The VLOOKUP can only search in the first column. What I need is to be able to find a value in column A and B and return the value from C.

=VLOOKUP("Corvette",A1:C2,3,0) returns #N/A (would like to return Chevrolet)
=VLOOKUP("Camaro",A1:C2,3,0) returns Chevrolet

Is it possible?

Upvotes: 1

Views: 88

Answers (3)

Terry W
Terry W

Reputation: 3257

You can also try this:

=IFERROR(VLOOKUP(F2,$A$2:$C$3,3,0),VLOOKUP(F2,$B$2:$C$3,2,0))

where F2 is the look up item, and $A$2:$C$3 is the range of your 3 columns.

Solution

The logic is to use two VLOOKUP to return the value from the 3rd column if the look up value is in Column A, or return the value from the 2nd column if the look up value is in Column B.

Cheers :)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152495

use AGGREGATE:

=INDEX(C:C,AGGREGATE(15,7,ROW($A$1:$C$2)/($A$1:$C$2=E1),1))

enter image description here

If it is only three columns then this will be quicker:

=INDEX(C:C,IFERROR(IFERROR(MATCH(E1,A:A,0),MATCH(E1,B:B,0)),MATCH(E1,C:C,0)))

But as you can see adding an IFERROR for each column can get out of hand with more columns

enter image description here

Both the above will return the first encountered of the lookup. If the data set is unique, no duplicates in any of the columns the we can use the following.

this uses FILTER which is currently available on Office 365 for Insiders:

=FILTER(C:C,(A:A=E1)+(B:B=E1)+(C:C=E1))

But it does require that the data set be totally filled with unique. If one want to return all that match we can use TEXTJOIN to create a comma separated list:

=TEXTJOIN(",",TRUE,UNIQUE(FILTER(C:C,(A:A=E1)+(B:B=E1)+(C:C=E1))))

Upvotes: 2

Selkie
Selkie

Reputation: 1255

I like index-match a bit more for this:

 =if(Isnumber(match(Thing, FirstColumn,0)),Index(ThirdColumn, Match(Thing, FirstColumn,0)),Index(ThirdColumn,Match(Thing, SecondColumn,0)))

Basically, test for existence in the first column. If its there, keep going, otherwise, use the second column.

Upvotes: 0

Related Questions