Forum Discussion
HELP PLS!! VLOOKUP not working at the end of a list
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
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
- 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?
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