Error while doing Vlook up ( table array not getting selected )

Question

Hi 

the Problem is i am comparing 2 excel sheets . one of them is file converted from .txt format to Xlsx format.  when i am trying to compare the 2 sheets i am getting a red selection column when trying to select the table array and when i enter only the first value shows the results and the rest give a #NA error , 

i have checked the formatting for both the sheets and formatted it to general , used text to column to eliminate spaces , brought the data on the same excel sheet but nothing is working. check that the table array is giving value in Red the format for all the sheet is the same still nothing is working. Working on excel 2013

Answer 1

In F2 use the formula 

=VLOOKUP(E2,$A$2:$B$91,2,FALSE)

or better still use

=VLOOKUP(E2,A:B,2,FALSE)

and copy the formula down...

As shown by you the range A2:B91 changes to A3:B92 when copied down, since you are not using the $ to indicate you do not want to change the lookup data range...

If this does not work then copy any value in Col A and paste it in E3... if you get the correct result in F3 then it means there is some difference in what you have in Col A and what you have in Col E.

Answer 2

1. CTRL+C in any blank cell
2. Select column A
3. Paste Special > Add
4. Input below formula in F2 and drag down -

=VLOOKUP(E2,A:B,2,FALSE)