Kapil
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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
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.
0
Great. It worked.
Thank you for the assistance.
Thank You
Kapil
Discussion
Don't forget to select his response as the correct answer :)
don (rep: 1989) Aug 5, '19 at 6:28 pm