Text and numbers in cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1590564%22%20slang%3D%22en-US%22%3EText%20and%20numbers%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590564%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everybody%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20building%20an%20Excel%20sheet%20with%20formulas%20containing%20VLOOKUP%20function%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EA1%20%3A%20100200%20(numbers)%3C%2FP%3E%3CP%3EA2%20%3A%20100200%20EXCL%20(numbers%20and%20text)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB1%20and%20B2%20%3A%20%22OK!%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EC4%20is%20input%20cell%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EResult%20formula%20shown%20in%20cell%20C5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20is%20input%20data%20in%20C4%20contains%20numbers%20and%20text%2C%20should%20result%20in%20same%20way%3C%2FP%3E%3CP%3Eif%20I%20type%20100200%20or%20100200%20EXCL%20it%20should%20show%20OK%20in%20cell%20C5.%3C%2FP%3E%3CP%3ERight%20now%20it%20shows%20%23VALUE!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20anyone%20some%20ideas%20how%20to%20fix%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1590564%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590607%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20and%20numbers%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590607%22%20slang%3D%22en-US%22%3Euse%20in%20C5%20%3A%20%3DVLOOKUP(C4%2CA1%3AB2%2C2%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590860%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20and%20numbers%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760489%22%20target%3D%22_blank%22%3E%40ArnovR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20that's%20only%20two%20cases%20when%20VLOOKUP%20returns%20%23VALUE!%20error%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fhow-to-correct-a-value-error-in-the-vlookup-function-1fabc766-32ae-4f7f-a2c4-d095153e6894%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fhow-to-correct-a-value-error-in-the-vlookup-function-1fabc766-32ae-4f7f-a2c4-d095153e6894%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20yours%20is%20second%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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 Replies
use in C5 : =VLOOKUP(C4,A1:B2,2,0)

@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.