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 sp...
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: