Hi, I created an excel field that has the option to lookup a code via dropdown o

Question

Hi,

I created an excel field that has the option to lookup a code via dropdown or type it in directly. Selecting a letter code or typing the code returns the correct lookup in the result box with =VLOOKUP(D5,$M$3:$N$34,2,FALSE). When looking up a number, like 9, the correct lookup value is returned; however, when the number is typed the lookup value returns #NA. I even tried =IF(D5=9,"CODE",VLOOKUP(D5,$M$3:$N$34,2,FALSE)) and I still get #NA if 9 is typed in D5, looking up returns the code.

Any suggegtions would be appreciated, I spent half a day searching and can't find a solution.

Thank you.

Explanation / Answer

Hello,

the data type of the lookup value is important.

If the values in column M are text, but the value in D5 is a number, try

=VLOOKUP(text(D5,"0"),$M$3:$N$34,2,FALSE)

If the values in column M are numbers, but the value in D5 is text that LOOKS LIKE A NUMBER try

=VLOOKUP(D5+0,$M$3:$N$34,2,FALSE)

You say that results are different "When looking up a number" and "when the number is typed "

How does "When looking up a number" happen? Are you using data validation with a list? If so, does the list contain numbers and text values?

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.