SOLVED

HELP PLS!! VLOOKUP not working at the end of a list

Copper Contributor

The formula works all the way down my data and then just returns #REF! for the last 4 entries but there is still data to read across both sheets sitting there available and it won't register it. I'm at a loss as to what to do. 

 

=IFERROR(VLOOKUP(A46,STOCK[#All],6),0)

 

It is meant to pull stock levels from one sheet and place it on our ordering sheet ('Manyara Order' or 'Walking Order') but it gives up when it gets to the last 4 values - 47, 48, 49, 50. Never experienced this before.

 

 

8 Replies
Hi. If the content of your workbook isn't top secret :) Could you attach it to your next reply please?
Could it be that the last 4 rows of your data aren't included in your table? the stock[] reference will only work in the table. Try resizing your data to include all of the data.

@L z. HI Thank you for taking a look - the stock is drawn from a google sheets and populated to a 'current stock' sheet within this workbook. It is the 'walking order (2)' where is just gives up at cell 180 using a vlookup but then reads the wrong data about 10 cells later.  It is so strange how it just won't work - all the data is there to be read - I have seen other posts online about this and then using some convoluted way to get around it but my excel is still very much on the starting blocks so coding and '?VGA?' goes completely over my head.

Hi yes its well above where the stock list table stops. Very strange.

@CateMil 

Ok. I took the freedom to delete a bunch of unecessary extra rows (see your attached file size now) but that didn't cause the issue

It took me about 5 minutes to understand which column/formula you had an issue with in 'WALKING ORDER (2)' and Yes strangely enough, even after removing the IFERROR around VLOOKUP I didn't get anything but something that looked like a "blank" value (same thing when pressing F9 in the formula bar)

Went to sheet 'CURRENT STOCK' and saw at the bottom a bunch of empty rows, but not all were empty, some had #REF! errors. And that's obvioulsy what caused the issue. After deleting those rows the formula in [Column1] sheet 'WALKING ORDER (2)' populated no problem

 

Double-check and let me know if anything is still wrong or if I missed something

 

Amazing - thank you!! The last few entries still read the supplier incorrectly - again not sure why. Any ideas? Do you think i am better using 'get data from web' for this list in the 'walking order' like i did for current stock instead of drawing it from the current stock page which draws it from GSheets?
best response confirmed by allyreckerman (Microsoft)
Solution

@CateMil 

I didn't pay attention to this previously. In sheet 'WALKING ORDER (2)' [Column1] and [Stock] your formula were (respectively) in row 10 (and then down):

=IFERROR(VLOOKUP(A10,STOCK[#All],2),0)
=IFERROR(VLOOKUP(A10,STOCK[#All],7),0)

 

The problem here is you don't provide VLOOKUP a value for its optional last parameter [range_lookup]. And the default value for the latter is TRUE/1 which means that the data in your lookup table must be ordered/sorted otherwise you get random/inaccurate results. [INGREDIENTS] are +/- well ordered in Table STOCK but the last few "items": CHARCOAL (BAGS) and KEROSENE (LITRES)

 

I updated your formulas in [Column1] and [Stock] sheet 'WALKING ORDER (2)' so they now say:

=IFERROR(VLOOKUP(A10,STOCK[#All],2,0),0)
=IFERROR(VLOOKUP(A10,STOCK[#All],7,0),0)

 

and magically enough :) this returns the correct SUPPLIER

Thank you so much! You are a wonder!!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@CateMil 

I didn't pay attention to this previously. In sheet 'WALKING ORDER (2)' [Column1] and [Stock] your formula were (respectively) in row 10 (and then down):

=IFERROR(VLOOKUP(A10,STOCK[#All],2),0)
=IFERROR(VLOOKUP(A10,STOCK[#All],7),0)

 

The problem here is you don't provide VLOOKUP a value for its optional last parameter [range_lookup]. And the default value for the latter is TRUE/1 which means that the data in your lookup table must be ordered/sorted otherwise you get random/inaccurate results. [INGREDIENTS] are +/- well ordered in Table STOCK but the last few "items": CHARCOAL (BAGS) and KEROSENE (LITRES)

 

I updated your formulas in [Column1] and [Stock] sheet 'WALKING ORDER (2)' so they now say:

=IFERROR(VLOOKUP(A10,STOCK[#All],2,0),0)
=IFERROR(VLOOKUP(A10,STOCK[#All],7,0),0)

 

and magically enough :) this returns the correct SUPPLIER

View solution in original post