Forum Discussion

stutwenty's avatar
stutwenty
Copper Contributor
Feb 27, 2024
Solved

Stuck on an issue with VLOOKUP

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...
excel
Formulas and Functions
  • Steve_SumProductCom's avatar
    Steve_SumProductCom
    Feb 27, 2024

    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.

     

     

rprsridhar's avatar
rprsridhar
Brass Contributor
Feb 27, 2024

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.

Resources