Forum Discussion
Stuck on an issue with VLOOKUP
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.
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.
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...?
- Do you think it would be easier if I cut and pasted the data onto one sheet?
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.
