Search code examples
excelexcel-formulavlookuplookup

VLOOKUP Not looking (dependent on two cells in one sheet plus another in a different sheet)

We have this data set

Picture 1

The "SOW" column is a drop down

We also have the lookups in a different sheet

Lookups

We want to be able to type a name in column A (that is already in Lookups), and then select a SOW type (cheese, bread, carrots etc.) and have the rate (the dollar amount in the lookups) pop up. Our vlookup isn't working, and we aren't sure why.

=VLOOKUP(A2,Lookups!A:N,MATCH(Data!E1,Lookups!C:I,0),FALSE)

Please note it's matching the name, not ID or other stuff. I can't seem to attach the excel sheet :(

Tried vlookup different one, we want to avoid getpivotdata because there's going to be a ton of entries under a bunch of different names

Solution

  • You could try using the following formula:

    enter image description here

    =XLOOKUP(A2,Lookups!A:A,FILTER(Lookups!C:N,Data!E2&" 24"=Lookups!$C$1:$N$1))

    And here is the quick fix to your existing formula:

    =VLOOKUP(A2,Lookups!A:N,MATCH(Data!E2&" 24",Lookups!$1:$1,0),FALSE)

    Or You can use wildcard operator *

    =VLOOKUP(A2,Lookups!A:N,MATCH(E2&"*",Lookups!$A$1:$N$1,0),0)

    Since OP says its not working as per their comments below for them here is another screenshot to validate the formula is working:

    enter image description here