Forum Discussion

ToddFleming's avatar
ToddFleming
Copper Contributor
Aug 02, 2024

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 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!!!

 

 

excel

5 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 02, 2024

    ToddFleming 

    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]]
    )
)

     

  • pdtcaskey's avatar
    pdtcaskey
    Copper Contributor
    Aug 02, 2024

    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]"

     

Resources