VLOOKUP error

Question

The only way I can get my vlookup formula to work is to manually type in the lookup number in the first column of my table, the number thats already there seems to be a value but I have tried several ways of converting the first column to number format but none of them work - it just keeps returning n/a# - is there a way I can get the first column to convert to number format so it works without having to manually type in the number? See attached screenshot

Answer 1

Hi Tom,

Greatly appreciate your prompt response and the screenshot.

Based on your sharing, it seems that the lookup value F7767 is a number and AA7767 is a text string. Since they are two different formatting type, VLOOKUP cannot treat them as the same.

Cleaning and converting your AA column would be not a viable resolution since it includes some value must be stored as text like FW47924. Therefore, as a simplest fix, you can try this formula:

=VLOOKUP(F7767&"", AA:AE, 5, FALSE)

You can also try this XLOOKUP formula as a cleaner version:

=XLOOKUP(F7767&"", AA:AA&"", AE:AE, "Not found") 

Please try it and let me know whether it works. If not, please don't hesitate to reach out again then I will assist further to efficiently resolve this issue.

Kind regards.

Answer 2

Hi Tom,

Based on your sharing, if the value looks like a number but VLOOKUP returns N/A error, Excel is probably storing it as text, or it may contain hidden characters like spaces or non-breaking spaces. Simply changing the cell format may not convert the existing text values into number.

You haven't attached the screenshot of your worksheet structure in your thread, so I'm not sure what exactly the formula and the data you're using. However, since it is suspected to be a text value, you can try using this formula to see if it works =VLOOKUP(VALUE(lookup cell), ...)

If it doesn't work, please share a screenshot of your current worksheet structure and the formula is currently used so I can clearly understand the situation to provide more appropriate guidance.

Please understand that my initial response may not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution. 

Thank you once again for your understanding and cooperation. I look forward to hearing from you. 

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.