Vlookup function returning 0 instead of the cell value.

New Contributor

Dear community. It is my first post so be patient with me

I have a problem with VLOOKUP function.

When I am typing in: =VLOOKUP(B2,hourly_stock_pl,3,TRUE) the function returns the correct values from the third column. However, when i am typing in: =VLOOKUP(B2,hourly_stock_pl,4,TRUE) the fourth column is returning 0 instead of the correct value of 20.

I also would like to attach the excel spreadsheet for you to look at but I do not know how '-.-

7 Replies


Drag and drop here or browse files to attach
Maximum size: 71 MB • Maximum attachments allowed: 5


01.11.2021 search value cell A14 
50 =VLOOKUP(A14,A2:D12,4,TRUE) 


Maybe you can paste your data into your post as shown in the above example.


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:!AhoU6FwcGDdkgke1Rqgj7No-V0l-?e=TQA77X

Thank you for the hint. I was afraid that it may not work since the sheet does not throw any errors and the formula is formed correctly. Nevertheless, I found a workaround, which is to upload the file to OneDrive and then share it with the following link:!AhoU6FwcGDdkgke1Rqgj7No-V0l-?e=TQA77X
best response confirmed by PeterK007 (New Contributor)


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.





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