VLOOK UP Function

[mmdmalta]

Hello, I am trying to figure out where i went wrong in a VLOOKUP formula. I followed instructions exactly, but I still get the N/A error. Here is what i'm trying to do.

I have 3 columns. Column A has the account number, Column B has the Name, Column C has the Age. I have a box where I want my formula to be typed up in column F3, and the name of the person that I want to type is in Column F2. The column is 3 columns wide, and 28 rows. Next to the Age column F3 is where i've entered my VLOOKUP formula. I appreciate the help! Thank you!


Column A Column B Column C Column F

Acct No Name Age Name: __________
Age : _=VLOOKUP($F$2,A2:C28,3,FALSE)__________
001 Janet Stephen 32
002 Keith Stephen 45
003 AJ Lira 45
004 Eleni Bordentious 52
005 Brad Staten 25
006 Jason Kelly 34
007 Danielle Kjullion 65
008 Tricia Conwell 22
009 Carissa DeMasi 44
010 Frank DeMasi 38
011 Freedom Evans 47
012 Alyssa Alona 70
013 Karen Yoshida 33
125468 Monica DeMasi 29
235689 Tanya Sorrell 35
112458 Andrew Mirelez 52
897456 Susan Olsen 51
475896 Sanford Gladding 48
231489 Carolyn Phillips 68
214231 Bernadette McCou 35
856412 Maggie Tristan 60
651242 Tom Klostermen 44
987789 Herb Sherman 43
222541 Randy Anderson 73
012536 Eddie Henderson 22
031245 Peter Kley 45
014781 Tonilee Krick 50

 

[Joe4]

I followed instructions exactly

Not quite. You missed one very important detail.
In VLOOKUP, you can only match on the LEFT-MOST column in your Lookup range.
So, if your lookup range is A2:C28, that would be matching on Account Number, not Name.
If you want to match on name, swap columns A and B in your LOOKUP range so that name is your left-most column in the lookup range.

See this for more details: https://exceljet.net/excel-functions/excel-vlookup-function

 

[Fluff]

Alternatively change your formula to
=VLOOKUP($F$2,B2:C28,2,FALSE)

 

[Joe4]

Alternatively change your formula to
=VLOOKUP($F$2,B2:C28,2,FALSE)

Good point. I was assuming that they might want to return the Account Number too, but in re-reading the original request, it doesn't mention that.
So, if they do not need the Account Number, this way would work fine without have to change data structure.
If they do need Account Number, then the would need to change data structure (if they want to use VLOOKUP and not some other formula).

 

[dcoker]

Try using INDEX and MATCH functions


=INDEX(C3:C29,MATCH(F2,B3:B29,))

 

[mmdmalta]

Not quite. You missed one very important detail.
In VLOOKUP, you can only match on the LEFT-MOST column in your Lookup range.
So, if your lookup range is A2:C28, that would be matching on Account Number, not Name.
If you want to match on name, swap columns A and B in your LOOKUP range so that name is your left-most column in the lookup range.

See this for more details: https://exceljet.net/excel-functions/excel-vlookup-function

Thank you! I understand now. I knew there had to be something i was doing wrong! This is very helpful. Thanks for your help!

 

[mmdmalta]

Thank you. When I changed the formula it worked! I understand now what I did wrong. I still think VLOOKUP is a bit confusing, but i now have a better understanding. Thank you Again!

 

[mmdmalta]

I haven't tried this one yet, as i used the others examples. However, I will keep this is mind, for other formula solutions. Thank you!

 

[Joe4]

You are welcome.

I still think VLOOKUP is a bit confusing, but i now have a better understanding.

Like anything else, the more you use it, the more you get comfortable with it.
They key thing to remember here is that whatever value you are looking up (the first argument) has to match up with the first/left-most column in your lookup range (second argument). And they both must be the same data type (if one is numeric and the other is text, it will not work).

 

[Fluff]

Glad we could help & thanks for the feedback