Stuck on an issue with VLOOKUP

[stutwenty]

This is sheet one:

The common data is in the second column (tradedCode). I am trying to fill in the free_stock column. 

This is sheet two:

The common data is the second column (SKU). I am trying to use the data from the column Stock level.

This is the formula I am using: =VLOOKUP(B2,Sheet2!A2:O25, 10, FALSE) as you can see from the screen shot I am getting a return value of #N/A

I don't know what I am doing wrong? 

[#2]

@stutwenty It appears that the tradedCode in Sheet one are text, but the values in Sheet two are numbers. 

You need to either need to convert data in one of the tables so you're dealing with the same data type, or you can do something like below.  The first argument of the XLOOKUP converts the text to a number by multiplying by 1.  

=XLOOKUP((B3*1),$B$10:$B$13,$C$10:$C$13)

I also recommend using XLOOKUP if you have it. It has a number of advantages over VLOOKUP, in particular, you don't need to specify a column index, so if you insert or delete columns in the range, it will continue to work.

[#3]

Hello @Steve_SumProductCom thank you for your help it is very much appreciated.

I have taken a few more screen grabs and you can see that the "b" columns are both "numbers" and I have also tried to add the *1 to the formula, but it still isn't working...?

[#4]

Do you think it would be easier if I cut and pasted the data onto one sheet?

[#5 · Accepted solution]

@stutwenty They are not both numbers. You can tell by clicking on any of the values that have the little green triangle in the corner. Then click the pop-up button that appears and I think it will tell you that they are "numbers stored as text".  Numbers are aligned to the right, and text is aligned to the left.

Also, the first column in the range of your VLOOKUP needs to be the one where you're trying to look up the values, so it would be B, not A, so your formula should be =VLOOKUP((1*B2),Sheet2!$B$2:$O$25, 10, FALSE).  Also, if you're copying the formula down, it's important to put the $ signs in the range reference. Just press F4 when your cursor is on the reference, and it will add those for you.

I still recommend using XLOOKUP, because you would avoid the problem of needing to refer to refer to the entire range B2:O25, and having the values to look up be in the first column of that range.

[#6]

Hello Steve, the XLOOKUP worked perfectly (once I figured it out using your diagrams!). Your help is really appreciated. Cheers. Stu

[#7]

@stutwenty 

The column you are looking up should be the first column. 

So look up table value should be B2:O25. 

Also make the reference absolute. i.e. $b$2:$O$25.

Also that will also change the column index number to 9 instead of 10.

I hope that should solve the problem.

[#8]

Thank you @rprsridhar !!