Text and numbers in cell

[ArnovR]

Hello everybody

I'm building an Excel sheet with formulas containing VLOOKUP function

Example:

A1 : 100200 (numbers)

A2 : 100200 EXCL (numbers and text)

B1 and B2 : "OK!"

C4 is input cell

Result formula shown in cell C5

What I want is input data in C4 contains numbers and text, should result in same way

if I type 100200 or 100200 EXCL it should show OK in cell C5.

Right now it shows #VALUE!

Have anyone some ideas how to fix this?

[#2]

use in C5 : =VLOOKUP(C4,A1:B2,2,0)

[#3]

@ArnovR 

In general that's only two cases when VLOOKUP returns #VALUE! error https://support.microsoft.com/en-us/office/how-to-correct-a-value-error-in-the-vlookup-function-1fab... 

Perhaps yours is second one.