V-Look Up Not working

[jlipien]

I have data in Sheet 1 Column A-P. Column D is a list with account numbers. In sheet 2 I have a mapping data. Column A is a type of account. Column B is account # and Column C is account description. In Sheet 1 in column Q I have following formula: =VLOOKUP(D2,Sheet2!A:C,1,FALSE)
I am trying to look up the account number from cell D2 in the Sheet 2 list and return value in column A which is the type of the account.
Basically I have a list with account numbers which have type of the account next to it and I am trying to assign account type to account number in my transaction list in Sheet1.
This is a very simple v-look up and I have done this many times when I have the data that I am looking up in another sheet. It always works, but this time it returns #N/A
I don't understand why. I checked for the cell formatting. Sheet 1 Column D is formatted as number and Sheet 2 Column B is formatted ad number, so it matches. I also tried moving my data around, because I know sometimes there's an issue that v-look up cannot look up to the left, but I don't think that should be the issue if I am looking up in another Sheet.
Please help. I am out of ideas.

 

[snjpverma]

vlookup will only work if your lookup value is in the leftmost column of your table array.

You should use Index & match instead.

=INDEX(Sheet2!A:A,MATCH(D2,Sheet2!B:B,0))

or else the below formula which will be slower

=VLOOKUP(D2,CHOOSE({1,2},Sheet2!B:B,Sheet2!A:A),2,0)

I have assumed that the value to be looked up is in Column B and the return value is in column A of sheet 2

 

[jlipien]

Thank you so much! The Index & Match does work! You've been a great help. I guess what I still do not understand is which part of the formula is the "lookup value" which needs to be in the leftmost column of my table array. I tried moving data from sheet 1 column D into Column A, because Column A is the leftmost column of table array. Now what I am trying to lookup is in Column A instead of Column D and I use this formula instead =VLOOKUP(A2,Sheet2!A:C,1,FALSE)
You solved my issue but I still don't understand why the V-lookup does not work.

 

[snjpverma]

if the value you are looking up is in Column A of Sheet2 and the return value is in Column B the below should work.

see the highlighted change

=VLOOKUP(D2,Sheet2!A:C,2,FALSE)

 

[jlipien]

Thank you so much! I finally understand why my formula was not working. I re-arranged Sheet 2 and now it works. I thought this part of formula needed to be to the left:
=VLOOKUP(D2,Sheet2!A:C,3,FALSE) and not this part of formula =VLOOKUP(D2,Sheet2!A:C,3,FALSE). Now I finally get it.

 

[snjpverma]

Glad you finally got it.