INDEX/MATCH #N/A error

[dcarbajal2006]

I am trying to use the index match function below to find a value. The values in the range Sheet2!F2:F6 are all characters but one. When the value of BODYMATRL is a character, the function works but when the value is a number it gives me an N/A error. Please help.

INDEX(Sheet2!G2:G3,MATCH(BODYMATRL,Sheet2!F2:F3,0))

 

[Marcelo Branco]

See if this works
=INDEX(Sheet2!G2:G3,MATCH(BODYMATRL&"",Sheet2!F2:F3,0))

M.

 

[Leandroarb]

Have you checked for any spaces at the beginning or end of BODYMATRL?

 

[dcarbajal2006]

See if this works
=INDEX(Sheet2!G2:G3,MATCH(BODYMATRL&"",Sheet2!F2:F3,0))

M.

Hello Marcelo Branco

I tried this and it is still giving me the same error. I may have to go back to VLOOKUP.


Thanks,

 

[dcarbajal2006]

Hello Leandroarb,

Yes, I checked. There is no extra spaces.

Thanks,

 

[Aladin Akyurek]

Does this work?

=LOOKUP(9.99999999999999E+307,SEARCH(" "&BODYMATRL&" "," "&Sheet2!F2:F3&" "),Sheet2!G2:G3)