A standard VLOOKUP would look something like the following. This is in cell B1

=VLOOKUP(A1,'DataSheet'!A:E,5,TRUE)

This would take the value in A1, match it against the same value in column A on 'DataSheet' and return the value from column E into B1. What I want to do is to find the value of column E, but where the condition is that column A should be X, column B should be Y, and column C should be Z.

I found this explanation that said "just use & in the condition", as in put X in A1, Y in B1, and Z in C1 and use the following:

=VLOOKUP(A1&B1&C1,'DataSheet'!A:E,5,TRUE)

However, this seems to ignore the values I use in B1 and C1 as it just returns the first found value where column A was matched, even if B1 and C1 does not match column B and C on 'DataSheet'. Is there a way to use multiple value lookups for multiple columns, but only return one value in Excel?

CodePudding user response：

Using that logic would require switching to INDEX/MATCH: =INDEX('DataSheet'!E:E,MATCH(A1&B1&C1,ColumnToMatchA1& ColumnToMatchB1& ColumnToMatchC1,0)) Replace the ColumnToMatch ranges with the columns you need to look for the matching values.