Forum Discussion
Vlookup, Index, Match
Excel idiot here..... In the pic below, on the sheet on the right, I am choosing two values in columns E and F from validation (reference the sheet on the left) and would like to return a value from...
ToddFleming: If the two tables are "Formatted Tables" (recommended), it would look something like:
=LET(
PositionCol,Match([@[Hourly Rate]],Table1[#Headers],0)-1,
VLOOKUP([@[Position Code]],Table1[Position Code],PositionCol,FALSE)
)Assuming the table with the rates is "Table1".
If your tables are simple ranges, you'd use the column and row identifiers:
Table1[Position Code] = 'Sheet Name'!$A2:$A10 (if the table is the upper left corner of "Sheet Name")
Table1[#Headers] = 'Sheet Name'!$A$1:$D$1
[@[Position Code]] = E3 (if entered in row 3)
[@[Hourly Rate]] = F3
Note that "Table" refers to only the data, not the headers. The entire table would be "Table1[#All]"