How to lookup multiple columns in excel-CodePudding

As the attached image, I can use the lookup formula to find out "Mary" by the value "D", however, is there any way I can also find out "Mary" by the value of "J", "P" or "V"?

CodePudding user response：

You can use INDEX MATCH based approach like this:

=INDEX(G1:G6,SUM((H1:L6=D1)*ROW(H1:L6)))

If you have a version of Excel older than 2016, you will have to enter this formula with CTRL SHIFT ENTER.

NOTE - this only works if the table starts in row 1.

So to make it more general, you could do:

=INDEX(G1:G6,SUM((H1:L6=D1)*(ROW(H1:L6)-ROW(H1) 1)))

CodePudding user response：

You don't need Lookup, but VLookup, let me show you:

=VLOOKUP(D4,$D$4:$H$20,2,FALSE)

This means (in case "Mary" is put in cell D4): go look in to D4:H20 (made invariable using the dollar signs), and take the second column (containing the "D") variable.

If you want to find "J" (third column), use:

=VLOOKUP(D4,$D$4:$H$4,3,FALSE)

If you want to find "P" (fourth column):

=VLOOKUP(D4,$D$4:$H$4,4,FALSE)

The last parameter is just for having an exact search.

CodePudding user response：

Another shorter option, can return single or multiple match.

In D2, enter formula :

=TEXTJOIN(", ",,IF(H1:L6=D1,G1:G6,""))

For Excel 2019, It will have to enter this formula with CTRL SHIFT ENTER.

For Office 365, it will normal entry.