Forum Discussion
Vlookup problems
I am trying to use Vlookup to create some calculations for some data, but the lookup value, is not always used, so I get the #N/A error.
I use a lookup which searches three separate sheets for a specific code, but I find the code used for the search in the sheets is not included in the data with the three sheets. From the look up two figures are calculated to find the difference, and if below zero, the data is set at zero.
The issue looks to be that the search field searches for a code, but when the data is copied into the tabs, the codes may not always show, so due to the data not available it creates the #N/A.
I have tried Using IFNA(formula to amend the error to 0), but it seems it is treating all the data as an error, and setting all the fields to 0.
INDEX / MATCH seems to advise too many arguments as an error for this to be used, but as I am not familiar with this formula, I am unsure if this is suitable for the type of data needed
The formula I have created only works if the look-up value exist, but they will not always show in the data I paste in the the file.
| =IF(VLOOKUP($B8,'22GP Raw data'!$1:$1000,4,FALSE)-VLOOKUP($B8,'22GP Raw data'!$1:$1000,5,FALSE)<0, 0,VLOOKUP($B8,'22GP Raw data'!$1:$1000,4,FALSE)-VLOOKUP($B8,'22GP Raw data'!$1:$100 |
I may have a solution, but it will mean more manual work to add, or remove dummy data to prevent the error happening.
I have added the excel file as an attachment so someone can view the data, and hopefully advise a solution.
If someone can advise on a better formula to make this file work I would appreciate it.
8 Replies
I'd scrap the VLOOKUP and use XLOOKUP which includes a built in error check.
As an example for the 22GP column:
=LET( inventory, XLOOKUP(B2:B5, '22GP Raw data'!A3:A1000, '22GP Raw data'!D3:D1000, 0), shipment, XLOOKUP(B2:B5, '22GP Raw data'!A3:A1000, '22GP Raw data'!E3:E1000, 0), remaining, inventory - shipment, IF(remaining < 0, 0, remaining) )I am getting a #SPILL error. Thanks for offering help, but looks like the only solution is to add any missing codes for the look up at the bottom of the range.
THe data should not reach 1000 lines that I am using so the easiest solution is to add the codes, so when it finds the details, then calculates the cells for the subtraction of the stock and the releases it will be 0 rather than creating the #N/A error.
The #SPILL indicates neighboring cells are blocking the output of the arrays. For example:
My solution.
=MAX(0,SUM(XLOOKUP(B2,'22GP Raw data'!$A$2:$A$5,'22GP Raw data'!$D$2:$E$5,0)*{1,-1}))Doesn't work, as formal is for too smaller data range. Tried amending to include the data range, receiving errors. In the message posted I was using row numbers for the Vlookup, as I am trying to create a formula that can be used for several purposes.
If you would use formatted Excel tables and structured reference in the formulas then there won't be a problem with a too small data range. The ranges will expand automatically.
You can wrap each VLOOKUP with IFNA or IFERROR to ensure a value of 0 is returned when not found.
Here’s a corrected formula that handles missing lookup values safely:
=LET( found1, IFNA(VLOOKUP($B8,'22GP Raw data'!$1:$1000,4,FALSE), 0), found2, IFNA(VLOOKUP($B8,'22GP Raw data'!$1:$1000,5,FALSE), 0), diff, found1 - found2, IF(diff < 0, 0, diff) )------------------------------------
Don't forget to mark as solution if my answer suits youDidn't work, and when used, it returned a 2 when it should have been 0. 'found and diff' I have never used, but will try to use some of this to see if I can find a solution.
