Issues with VLOOKUP function
Hi,
I'm trying a formule using the VLOOKUP function and I have some troubles about the result:
When VLOOKUP searh for a text in a cell it works fine but if this cell has a number it doesn't work. However, if I put this number in the formule it wors fine.
Can you help me about this?
Thanks in advance.
Regards.
this is working:
not work:
Putting the number in formule, it works
Search table
Best Answers

It looks like the cells with only numbers in your search table are recognized as numerical, but in your sheet with the formula your Serial Number column is reading all of the numbers as textvalues. You can tell this because they appear rightaligned in the search table, versus when they're recognized as text and appear leftaligned.
(See the difference in placement from the "CV05104" value and the "2019034484" value?) This is why when you type in the number in the formula it can find it.
Can I ask how the Serial number is being input, and how the "Numero de serie" in the table is being input?
You could potentially wrap the VALUE function around the cell you're referencing, like this:
=VLOOKUP(VALUE([Serial Number]@row); {Lookup Table}; 6; False)
But then you'd get an error for the cells that have text with the numbers. To adjust this, add an IFERROR statement around it, to show that we only want the VALUE if the text is all numbers:
Full formula:
=VLOOKUP(IFERROR(VALUE([Serial Number]@row); [Serial Number]@row; {Lookup Table}; 6; False)
Let me know if this works for you!
Cheers,
Genevieve

Hi,
It work's fine.
Thank you.
Answers

It looks like the cells with only numbers in your search table are recognized as numerical, but in your sheet with the formula your Serial Number column is reading all of the numbers as textvalues. You can tell this because they appear rightaligned in the search table, versus when they're recognized as text and appear leftaligned.
(See the difference in placement from the "CV05104" value and the "2019034484" value?) This is why when you type in the number in the formula it can find it.
Can I ask how the Serial number is being input, and how the "Numero de serie" in the table is being input?
You could potentially wrap the VALUE function around the cell you're referencing, like this:
=VLOOKUP(VALUE([Serial Number]@row); {Lookup Table}; 6; False)
But then you'd get an error for the cells that have text with the numbers. To adjust this, add an IFERROR statement around it, to show that we only want the VALUE if the text is all numbers:
Full formula:
=VLOOKUP(IFERROR(VALUE([Serial Number]@row); [Serial Number]@row; {Lookup Table}; 6; False)
Let me know if this works for you!
Cheers,
Genevieve

Hi, Genevieve P.
Thank you very much for your answer.
I've been testing, but it still doesn't work.
I will prove to add a column formed by "sn + [Serial Number]" and I will tell you if it works. I hope so.
Regards.

Hi,
It work's fine.
Thank you.

Hi Juan,
Glad you got it working!
Help Article Resources
Categories
Check out the Formula Handbook template!