VLOOKUP #NA Error

0
I have a query  for VLOOKUP with attached file.   I need data in sheet name-Sheet2 from sheet name-ria.   So i have applied VLOOKUP formula in Sheet2 in column B.   But i am getting #NA error.   Please help me for this error.   Thank You

Kapil

Answer
Discuss

Answers

0
Selected Answer

Your formula is correct but it can't deal with the leading apostophe used in Sheet2!A:A. Observe that cells in ria!B:B are formatted as Text and cells in Sheet2!A:A are formatted as General and converted to Text by the apostophe. I tried Copy / Paste special > Values to convert the format but to no avail. I tried changing the cell format - no success. But when I copied a number from sheet ria and inserted it in Sheet2 following the apostrophe the apostrophe was removed, even while I tried to retain it, and your formula worked for that value.

I concluded that the apostrophe might easily get lost and, therefore, suggest the following formula which works both with and without leading apostrophe.

=VLOOKUP(IF(CODE(A1)=39,MID(A1,2,100),A1),ria!$B$2:$E$2618,4,0)

This formula checks if there is an apostrophe (CODE(39)) and drops it from the lookup value if found.

Discuss
0

Great. It worked.

Thank you for the assistance.

Thank You

Kapil

Discuss

Discussion

Don't forget to select his response as the correct answer :)
don (rep: 1715) Aug 5, '19 at 6:28 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login