Issues with VLOOKUP function

Juan Carlos Reguera

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

Genevieve P.

Hi @Juan Carlos Reguera

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 text-values. You can tell this because they appear right-aligned in the search table, versus when they're recognized as text and appear left-aligned.

(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

Juan Carlos Reguera

Hi,

It work's fine.

Thank you.

Genevieve P.

Hi @Juan Carlos Reguera

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 text-values. You can tell this because they appear right-aligned in the search table, versus when they're recognized as text and appear left-aligned.

(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

Juan Carlos Reguera

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.

Juan Carlos Reguera

Hi,

It work's fine.

Thank you.

Genevieve P.

Hi Juan,

Glad you got it working!