Vlookup function returning 0 instead of the cell value.

[PeterK007]

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

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 '-.-

[#2]

@PeterK007 

Drag and drop here or browse files to attach

Maximum size: 71 MB • Maximum attachments allowed: 5

[#3]

@PeterK007 

column1	column2	column3	column4
16.08.2021	76	south	60
27.09.2021	44	south	90
10.10.2021	56	north	10
12.10.2021	82	north	100
15.10.2021	61	east	80
27.10.2021	52	south	30
30.10.2021	41	east	50
06.11.2021	13	east	110
23.11.2021	3	north	70
26.11.2021	13	north	40
06.12.2021	33	east	20
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.

[#4]

@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

[#5]

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

[#6 · Accepted 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)

[#7]

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?

[#8]

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