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 a sheet on the left. i.e. If I choose "ELDC" and "OT Rate" on the right, I would like choose the proper value from the table on the left...in this case $31.76. Any help is appreciated!!!
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]"
There's several ways to tackle this one. I prefer using 365 functions when possible so I'll offer XLOOKUP.
This solution presumes the lookup range is a table called Rates.
=XLOOKUP(
I2,
Rate[Position Code],
XLOOKUP(
J2,
Rate[[#Headers],[Regular Rate]:[Emergency Rate]],
Rate[[Regular Rate]:[Emergency Rate]]
)
)
