Forum Discussion
Vlookup function returning 0 instead of the cell value.
None of the part numbers in column B of the Destination sheet match any of the values in the first column of hourly_stock_pl.
Use
=VLOOKUP(B2,hourly_stock_pl[[p_pn]:[imStock]],2,FALSE)
and
=VLOOKUP(B2,hourly_stock_pl[[p_pn]:[imStock]],3,FALSE)
Thank you for the reply. Sadly, I do not see the browse function. When trying to drag and drop it does not upload. However, I found a workaround, which is to upload file to onedrive and then share it here with the link: https://1drv.ms/x/s!AhoU6FwcGDdkgke1Rqgj7No-V0l-?e=TQA77X
None of the part numbers in column B of the Destination sheet match any of the values in the first column of hourly_stock_pl.
Use
=VLOOKUP(B2,hourly_stock_pl[[p_pn]:[imStock]],2,FALSE)
and
=VLOOKUP(B2,hourly_stock_pl[[p_pn]:[imStock]],3,FALSE)
- Hi, Thank you very much. It does solve my problem and it works 🙂 providing correct values.
I would be even more grateful if you could explain to me why the previous formula did not work and why adding |[[p_pn]] to the formula works. What does it do?Your original formula =VLOOKUP(B2,hourly_stock_pl,3,TRUE) looks up the value of B2 (a part number) in the first column of hourly_stock_pl, that is the p__id column. It won't find it since p_id has two characters before the part number. The actual part numbers are in the second column of hourly_stock_pl, the p_pn column.
And since we now start at the second column, we need to subtract 1 from the column index: 2 instead of 3.
Apart from that, using TRUE as fourth argument of VLOOKUP causes Excel to look for the last value that is less than or equal to the lookup value, and this is only useful if the lookup array is sorted ascending on its first column. For an exact match, use FALSE as fourth argument.
If you have Microsoft 365 or Office 2021, you can use
=XLOOKUP(B2,hourly_stock_pl[p_pn],hourly_stock_pl[eancode])
and
=XLOOKUP(B2,hourly_stock_pl[p_pn],hourly_stock_pl[stockFree])