SOLVED

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

@PeterK007 

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

@PeterK007 

column1column2column3column4
16.08.202176south60
27.09.202144south90
10.10.202156north10
12.10.202182north100
15.10.202161east80
27.10.202152south30
30.10.202141east50
06.11.202113east110
23.11.20213north70
26.11.202113north40
06.12.202133east20
    
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.

@NikolinoDE 

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

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: https://1drv.ms/x/s!AhoU6FwcGDdkgke1Rqgj7No-V0l-?e=TQA77X
best response confirmed by PeterK007 (New Contributor)
Solution

@PeterK007 

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?

@PeterK007 

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])