Forum Discussion
HELP PLS!! VLOOKUP not working at the end of a list
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.
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 https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 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
8 Replies
- 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.
- Hi yes its well above where the stock list table stops. Very strange.
- Hi. If the content of your workbook isn't top secret 🙂 Could you attach it to your next reply please?
Lorenzo 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.
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
