VLOOK UP Function

mmdmalta

New Member
Joined
Oct 9, 2006
Messages
38
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,296
Office Version
  1. 365
Platform
  1. Windows
Alternatively change your formula to
=VLOOKUP($F$2,B2:C28,2,FALSE)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 13, 2018
Messages
25

ADVERTISEMENT

Try using INDEX and MATCH functions


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

mmdmalta

New Member
Joined
Oct 9, 2006
Messages
38
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

New Member
Joined
Oct 9, 2006
Messages
38

ADVERTISEMENT

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

New Member
Joined
Oct 9, 2006
Messages
38
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,296
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,876
Members
409,668
Latest member
mitunsLax

This Week's Hot Topics

Top