SOLVED

Error in VLOOKUP #N/A from a Table

Copper Contributor

Hi,  I have an error with the formula VLOOKUP...
I need a "search cell"  and when I'm  doing the formula:

 

=VLOOKUP($V$44,'TABLE '!A2:U146,6,FALSE) 

                              or

=IFERROR(VLOOKUP($V$44,'TABLE '!A2:U146,6,0),"")

 

In the receipt cell shows the first name of the column 6  - (When the "search cell" is in "blank")

In the receipt cell shows #N/A if I capture the order number (Código)image.pngimage.pngimage.png

 

Someone please help me with this... I need your help

 

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@vegedgar VLOOKUP expects the find the lookup value "New Appearance" in the first column of the lookup range. In your case the first column contains the "traffic lights". Thus, a match will never be found. Change the lookup array to start at F2. But, if you want to return the Order Number it will be outside the lookup array. Without some special tricks, VLOOKUP can not lookup towards the left. The easiest would be to move the Order Number column to the right of the Name column, and then use:

 

=VLOOKUP($V$44,'TABLE '!E2:U146,2,FALSE) 

 

If you are on a modern Excel version use XLOOKUP in stead. You can find all about it on-line.

@Riny_van_Eekelen   Thank you very much Dude !!! You are the best !

I was move the column Order# to the first column and now it's working ...

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@vegedgar VLOOKUP expects the find the lookup value "New Appearance" in the first column of the lookup range. In your case the first column contains the "traffic lights". Thus, a match will never be found. Change the lookup array to start at F2. But, if you want to return the Order Number it will be outside the lookup array. Without some special tricks, VLOOKUP can not lookup towards the left. The easiest would be to move the Order Number column to the right of the Name column, and then use:

 

=VLOOKUP($V$44,'TABLE '!E2:U146,2,FALSE) 

 

If you are on a modern Excel version use XLOOKUP in stead. You can find all about it on-line.

View solution in original post