Last month, I wrote a short and simple example of how to use the VLOOKUP function in Excel and promised to follow up with a second part.
In this post, we’ll look at a slightly more complicated example and show a couple of tips and tricks for making VLOOKUP work correctly.
By the way, I’ve received a couple of comments and thanks for my previous post and just want to encourage readers to let me know if there are other examples of functions or situations they face that they need help with. They make a great source for future posts on this site :)
In our last example, we had a simple, two-column list of names and types of animals. In this post, we’ll take a look at a list of employee names and data, say, for calculating commissions for sales people. Here’s what our data looks like (on all images in this post, click to enlarge):
As you can see, we’re given employees’ last and first names, their base salaries, their bonus percentage, and the % of the year that they were employees. We’re also given a unique identifier in the form of an employee number. Let’s examine the data a bit further.
First, what we should notice is that there are employees with the same last and first names. There’s an Andrew Anderson as well as an Andrew Cobb. And a Penny and Jim Dee.
Remember that VLOOKUP will either return the first match it finds in a list. In this case, if we were to use VLOOKUP to lookup a list of last names or first names, VLOOKUP would always return Andrew Anderson’s data (if we were looking using the “First Name” field) or Penny’s data (if we were looking using the “Last Name” field).
So, what to do?
In this example, we’re lucky to have a unique identifier in the form of “Employee Number”. Each number is assigned only once to the employee, so this field would be a safe one to use for VLOOKUP. The only problem is that it’s located all the way at the end of the data, to the right of all the other fields. Remember that VLOOKUP has another criteria: whatever field you’re using to look up other data has to be to the left of all the other fields.
The easiest way to accomplish this is to insert a column to the left of “Last Name” (Column A) and copy-and-paste the “Employee Number” column there. Here’s how that would look, step by step:
Step 1: Select column F, where “Employee Number” data is located:
Step 2: Right-click on the mouse:
Step 3: Select “Copy” from the menu:
All of column F is now highlighted in a dotted line:
Step 4: Highlight column A:
Step 5: Right-click on the mouse once more:
Step 6: Select “Insert Copied Cells” on the menu
Step 7: The cells from column F are now copied over to column A, and everything is shifted over one column:
Now, employee numbers appear in both column A and G. Hit
We’re now good to go!
By the way, if you had not had unique identifiers like employee numbers readily available, you could potentially use the CONCATENATE or “&” function in Excel to “create” unique identifiers. CONCATENATE is a function that just munges two fields together. In this case, creating a unique identifier out of concatenating last name and first name would probably work.
Back to the tutorial. Suppose we had a second sheet that had a list of employee numbers for the four employees who had worked less than 100% during the year, and we wanted to calculate their bonuses for the year. Notice we swapped first and last name orders in this sheet and put the employee numbers in a different order:
We just want to fill in the data from the other source (possibly from another Excel sheet or workbook) in order to do the calculation. Here, I’ve left the original data in “Sheet1″ and am pulling the data into “Sheet2″.
What would we put into cell B2 in order to pull the data correctly? In the example in the last post, we only had two columns of data. In this example, we have seven, after copying over the employee numbers into the first column.
For the “First Name” column, we would be pulling data from column 3 in our data set (not column 2, which contains last names). So here’s what the VLOOKUP would look like for cell B2:
And here’s the excel step-by-step:
Step 1: Insert the VLOOKUP function by typing “=vlookup(” in cell B2:
Step 2: We’re looking up Employee Numbers, which are located in the previous column, so we put in A2 for cell B2:
Put in a “,” after this to move on to the next input for VLOOKUP called “table_array”.
Step 3: Now we need to highlight the area where all the data resides:
Put in a “,” after this to move on to the next input for VLOOKUP, called “col_index_num”.
Step 4: Remember that in this case, we need to reference column #3, where first names are located. We always start with the lookup value as column #1 and count toward the right.
Put in a “,” after this to move on to the final input for VLOOKUP.
Step 5: Finally, we want to put in “false” as the final input into VLOOKUP to tell it to look for exact matches.
Now close off the parenthesis to VLOOKUP, and the cell is automatically populated with the data we need.
The key now is to populate the rest of the cells. Can you figure out how to do this? One way would be to go through each cell and repeat the steps above. For example, to populate cell C2, we would write:
and so on, referencing each column where the data resides. (“Salary” resides in column 4, “bonus” in column 5, etc.) Another way would be to use Excel’s anchoring mechanism so that we could copy and paste formulas a bit more efficiently.
For example, for the rest of the cells under “First Name”, what we could do is write the following instead in B2:
What putting a “$” sign does in front of cell coordinates is to “lock” them in place. By putting $A2 instead of A2 in the first input section, we lock “A” in place (because all our employee numbers are in column A) and let the “2″ change as we go down the row.
By putting “$A$1:$G$8″ instead of “A1:G8″ as we originally had, we lock in the entire A1 to G8 cells in place and keep that section “locked” no matter where we put the formula.
If we then copy the formula down to cells B3 through B5, we don’t have to retype the formula each time. Similarly, you can copy the formula across each row, making sure to just change each column number so that you’re pulling the right data.
Here’s what the finished table would look like:
And here’s what the final column, which is just the total bonus calculation, would look like if we assumed that bonuses equaled salary * bonus * % of year worked:
In this example, we populated a new table in a new sheet with data from a separate sheet. But keep in mind one of the powerful things of VLOOKUP is that with a unique identifier such as “Employee Number”, what we could do is create an entirely new table with elements from multiple other tables that each contain “Employee Number”. For example, salary information might be stored in one place, and employee names in another. By using VLOOKUP to lookup employee numbers from each table, we could create one table that contains all information at once.
This has been a pretty lengthy example that’s actually covered a lot of different Excel tips, so we’ll stop here. If you have any specific questions or examples that you wouldn’t mind sharing (of course, all data would be disguised and I’d get your permission first), I’d be happy to answer them by creating a post with your example in the center.
Popularity: 7% [?]
Livepaths
Great blog!
If the economics don’t work, recycling efforts won’t either.
Http://LivePaths.com blogs about innovative people and companies that make money selling recycled or reused items, provide green services or help us reduce our dependency on non renewable resources. These includes some very cool Greeninvestments opportunities.
Firoz Alam
Very Usefull……i am very galde to know that i can learn a lot through this valuble site.
denisha
i need your help please
harmain
thank for help vlookup and i want help similarly in hlookup and macros so please help me by suggesting some side or by your mail sir thank for all these harmain never forget what you give to me sir
Duy Nguyen
This is great ! Thanks a lot for your valuable time and instructions !
Ginny
Thank you for your help. I was struggling with a Vlookup formula and the simple instruction and detail explanation has helped me tremendously. I am experiencing problems with a vlookup that is not finding the values due to the cell format. ie: cell value 4239 in a general format and the value in the lookup is 4239 and the formula is not returning the value correct. I am sure it is reading one as numeric or something. Please let me know if you can help or explain
ricemutt
@Ginny: Yes, this is one of those “wonderful” quirks of Excel where two numbers that should match don’t. First, make sure there aren’t any spaces or other characters like ‘ at the beginning or end of the numbers. Once you’re sure these are gone, the quickest way I’ve learned to force matching of numbers is to create a new column that equals 1* the number (e.g. if you have 4239 listed in A1, in B1 put “=A1*1″ to force it into a numerical format. Do the same for the other table you’re trying to match to in a new column. Then vlookup the figures in each new column against each other and they should match up. Hope that helps!
Sumeet Chandra
very useful indeed. Thanks for all ur efforts.
Cheers!!
Jan
Thanks for this. It has been the easiest, most helpful explanation of VLOOKUP I have found so far. I really appreciate you taking the time out to create these tutorials. :)
Kavita
This is awesome…i had worked on excel 2003 some time ago…and thought it would be easy for me to do vlookup…i was wrong..thanks to your amazing efforts…i was able to work it out finally
Raina
I need your help!
Scott
Could you show some examles of vlookups using And,OR,IF statments
Wally
thanks for the help!
Balvant
Simple Elegant Explanation!!
JIS
I HAVE A QUESTION IN EXCEL .. HOW CAN WE MAKE VALUES “YES” IF THEY EXIST IN THAT COLUMN. WE WANT TO HIDE DATA AND JUST WANT TO PUT “YES” FOR THAT DATA.
PLEASE HELP ME.
Pravin Thakkar
Wow this was easy to understand. I will save lots of time for my application at work.
Great Job.
j
how would one write the formula to look up values on a different drive and folder? looking values on the same sheet is easy.
Anil Pillai
Thanks again, dear sir, for setting a beautiful example to learn easily and interestingly.
Anil
Tonya
I can not understand why my vlookup continues to show up with N/A. I’ve read and copy the same steps and still there’s issues ….I beleive I’m trying to hard or something..please email me back
thanks
tonya
abdel
Good one ,thanks
Nikhil Lamba
That was great. Can you help me with a formula which checks the 2 columns and then return the result because some times you might have first name as common (as indicated above) and you need to check any other column before returning the answer. (any formula apart from concatenate).
Anne Sallee
Your assistance on this would be HUGELY apppreciated!
I have two very large spreadsheets.
Sheet 1 is a running list of expenses paid as they come in. Sheet two keeps a running total of expenses paid for each matter. Matters are refered to by reference number.
As the new expenses are entered into sheet 1, I would like to be able to add the individual amounts to the correlating row (by reference number) on sheet 2 into Column U, where there is a running total of expenses to date.
Is that possible? It seems like it should be but that is beyond my elementary Excel capabilities…
Your assistance on this would be HUGELY apppreciated!
Riad
Very helpful I must say. don’t know if it’s possible but when you make theses learning web pages can you also attached the blank excel worksheet template, with the guideline ? This I think will make it allot faster to do the example. Once again very helpful like how you slip other excel stuff into the examples
Christina
Your examples and tutorials are very good. I am getting very close, however, this column reference piece just does not seem to ‘work out’ in any way I try to write this VLOOKUP formula. I have finally gotten it to return ’0′ instead of N/A, REF, NAME or VALUE so I know I am close. I just need some assistance on the final piece. Am I over analyzing this? Any assistance would be greatly appreicated.
Thank You!
Andrew Beyer
I have a CSV file exported from the register system computer, and want to take an excel usage program to pull data from one program into another. the CSV file is a list of products, their cost, and how many sold that day. in the CSV file is layed out as followed:
A: Date
B: Product Code Number
C: Usage
D: Price
E: Discription
I want to look for the product description “Whopper JR” and tell me the usage.
I can give you a sample of my CSV file and Excel File if you need to look at it.
Every Month we add new products and the row number changes too, so next month say location C132 use to be the usage for whopper jr, is now the usage for hambergers.
anyone please help!!!!
Courtney MCshane
I am working on using a v-lookup but am having trouble. I have an identifying number that is the same number for 10 different names. HOwever the names are also the same but there is only one within each identifying number. I would like to populate a tab by saying place this $ amount in the cell if 3456 says Permit number. I need both of these sells to be correct in order to v-look up the right amount. Do you have any suggestions?
KUMAR
Realy thanks for ur tips. its very usefull for me.
Jourden
I totally agree with the other comments here, the steps are very easy to follow. There is no such thing as too simple and its exactly what you need to get the concept. Its good that every little step is shown – I don’t think that anything should ever be assumed.
Mandar
Really good info…..
Megha
Thanx a lot.I have been trying to learn this function for past 1 yr but it took just 5 min using this article to learn vlookup function.
Greg
Very clear and concise instructions. Many thanks!
Lizy
How about a sheet with more than 1 same words. For example, I need vlookup the qty for each goods located in different warehouse. Some goods may exist in 2 warehouse, when I vlookup, it will always show the first warehouse data and ignore the second one.
Srinivas
HI, Please help me to solve one vlookup problem. I want to vlookup the second occurance. For example:
a-1
b-2
c-3
d-4
a-5
b-6
I want the return value for a should be 5 not 1. I appreciate your help. Thank you and I request you to send the response to my email ID mentioned above.
Rajiv
Its just amazing…..You made it vlookup so simple
Its great
Dawood Mamedoff
Hi, thanks for explanation! For simpler illustration of how Vlookup works I’d also recommend the following video tutorial:
http://www.myhowtoos.com/en/excel-howtoos/84-how-to-match-values-in-excel-using-vlookup
Nina
Thanks a lot! for making vlookup formula so easy!!! Hope you have many more formulas in simple to learn. Thanks again.
arthur
Hey, thanks for the great tutorial, always great to learn from them. I have a problem, and I think it is similar to Lizy. I am trying to vlookup items. Many items have same numbers but different descriptions and ordering date. Is it possible to vlookup and display more description results for items with same numbers?
Jane
Thanks so much. I’ve always dreaded VLookup or other functions. You made it so clear and easy!
Mike C
Well laid out and very helpful. I am still learning this function, and one of the things that tripped me up is comparing a text field with a value field. It might be helpful to note that sometimes, you get a false #N/A because of these differences. Example: I was getting false #N/A and knew I had written the vlookup correctly. A colleague changes the searched field to text, and BAM…it worked. Again, nice work and thank you very much for the post.
Hari
This is really intetesting and everlasting article for Excel biginers
A.Peter Titus
Thanks a lot! for making vlookup formula so easy! Hope you have many more formulas in simple to learn. like a play Thanks again.
Shankar
Hi Les,
Thanks for your simple Vlookup tutorial. Made things easier for me. I would very much appreciate if you could do simple tuto for Pivot Table & Chart – how to build the table using multiple consolidation ranges.
Muzakir
could u please show me,if we don’t have a unique data how to loop the VLOOKUP formula get the second matching value
A B
andrew john
andrew Robert
vlookup will only give andrew John as result , how can we can both the results
as
Andrew John
Andrew Robert
if we don’t have a unique data
i want vlookup formula to not to stop in its first lookup value, it should lookup the second value too with the same data , is it possible…???
Sahil
Thanks…
I have learnt vlook up by reading ur tutorial, i would be more greatfull if you could provide me some usefull tutorial base on excel such as (if formula) or any other formula…
Thanks a lot again waiting for ur reply..
S Ganesh
It is very really nice to learn
i expecting you how to lookup values different excel files
can you send me email
Thanks
Ganesh
santy(santosh)
I am very greatful to you since you have provided such an easy steps and proper guidance of how to use vlookup… thank a lot
sivaraman.p
thank you so much…great information
neerja mishra
really its easiest way.pls provide h look up…………….u r great
Khaja Naseer Uddin
I want to make a table that is containing a Unique name is repeted 2 or 3times but each time it is containing some information how can i combine all that data in one place.
Exp.
Emp.Name Basic Food Al.
Naseer 50
Syed 20
Farooq 1000 40
Ahmed 1100
devrao 900 60
amar 800 70
Naseer 10
Ahmed 50
Karunakar 100
Karunakar 55
Syed 950
And the result Should be like this
Emp.Name Basic Food Al.
Ahmed 1100 0
Amar 800 70
Devrao 900 60
Farooq 1000 40
Karunakar 100 0
Naseer 50 10
Syed 950 20
please give me some tips to make this table.
Thanking you.
anand
thanx a lot.
Noe i m very much clear abt vllokup but one doubt i.e.
Suppose in the above data ther is no unique idetifier(i.e.emp. number) mention, then how can i do the vlookup by using CONCATENAT function?
please help me out.
Thanking You.
K R JAYANTHAN
good
SUMIT GARG
THANK YOU SOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
MUCH
Seanne
Thank you so much…
This site help me to learn more about Vlook up…^___^)
Could you teach me about vlook up with fungsional IF (in excel), if they use in one formula?
Thanks a lot…God bless you
Mukesh
Thank you so much…
This site help me to learn more about Vlook uu
Could you teach me about vlook up with fungsional IF (in excel), if they use in one formula?
Many Many thanx Dear………………………………
gopalakrishnarao.v.
Excellent and simply marvellous.
RAMKESH svpsl
So good is the vlookup commands
Thanks God.
Manoj
it was very nice idea to teach some one .
i want to also learn like that .please teach me how to setup the month formula one tab to another tabs
Roger
Wonderful detailed description – yet very easy to understand.. KUDOS..!!
My only issue is that i am attempting to create a > or < function, and i am having problems with this.
Else-wise your site has truly helped me get as far as i am, with little to no hair pulling..keep up the great work..!!
Praveen Gupta
Dear Sir,
its simple and step by step learing, really appreciate you effort.
Further can you guide us how to mix up a data in a sheet spreading in many excel sheet.
“But keep in mind one of the powerful things of VLOOKUP is that with a unique identifier such as “Employee Number”, what we could do is create an entirely new table with elements from multiple other tables that each contain “Employee Number”. For example, salary information might be stored in one place, and employee names in another. By using VLOOKUP to lookup employee numbers from each table, we could create one table that contains all information at once.”
jitender kumar
Dear sir,
I use this at fedora but its not working.pls give it to my mail id.pls
iTropics
Thanks for this. This is what I am looking for…Nice one…
devender shekhar
This is very good step by step simple guidance for initial users of vlookup. But pls guide me how to use it for different workbooks. Cursor does not highlight data contained in a sheet of another workbook. For different sheets of same workbook, it is working…..but not for different workbooks…..am using ms excel 2007. If some settings are required to be done in my excel………pls let me know.
Thanx.
David Waldron
thanks for the tips, one of those functions I use only occasionally & have to re work it out each time I use it, it was the false statement (missing) at the end that was giving me grief
Manish Jangra
thanks it is very usful to me….
Vinayak Bhat
Dear Sir
Thanks a lot. Your tutorial on excels vital functions like Vlookup, if, pivot table etc are very useful. Please write more. Thanks
Auro
Very Informative & step by step guide. This is great learning for new kids in excel world. Can you please cover Macros as well?
Chloe Lam
Thanks heaps for sharing this. Your step by step explanation are very to understand.
I have a similar challenge to the one that Khaja Naseer Uddin posted (on10 October 2009 at 0:05) above. Could you please provide your assistance with this?
Many thanks
CL
John DIT
Please, Help me! It has block me to finish my work. I have told to work out the gross and net pay for each staff member using the following tax rates: (use VLookup).
$0.00 $5,999.00
$6,000.00 $20,999.00
$21,000.00 $57,999.00
$58,000.00 $70,000.00
now, the problem is with those taxt rates and vlookup. how can i do this calculation?
Just to give you an idea about th table: it s layout like this:
Staff No. Name. Hrly rate. Hrly work.
12345 john $40.00 100
—- ———————————
—————————————-
Help please.
Fulham yoof
Brilliant. Thank you.
Now for what ifs!
Ganeshan
Thanks
Kailee
Hello,
I am hoping that you can help me. I currently have a drop down list with approximately 250 items. I am trying to figure out how I can type in the cell with the drop down and have it call up the items based on the spelling rather than having to scroll through the long list to select what I want. Thank you in advance!
Kailee
Aga
It has beed very useful and so easy to understand. Thank you for doing this!!!
Sneha
great i need basic help and why do we use vlook up also in need to know if i have a mastersheet and a differet exel file, how to find that what all data are missing in exel file which is there in master sheet
Mayank
Hi, Thanks for the wonderful explaination.. I wish to add the values that are obtained using Vlookup. I have a table that has account numbers, amount and the labels according to the amount. So suppose there is a label name ‘B’, then there are multiple entries for the same. I wish to add the values of all the label ‘B’. Is it possible? Please replt asap.
Thanks.
Kate
This is so helpful, thank you! I’m an IT trainer but haven’t used VLookup for ages and have to teach it in a couple of weeks. Your examples are really clear and well explained – thanks again!
Bruno H.Michele
Thank you very much!
Your tutorial helped me to understand what was wrong with a crippled excel file we received and that no one here was able to understand, let alone fix it.
Thanks to you I carved my way through the formulas, realized how they worked and finally amended the file.
vishnu pratap narayan
send ms office related formulas
Joyih Sri
This post is so helpful. Thanks so much for your effort and time to make it so easy to understand. I am looking forward to learn more from you. Thanks again.
Vishnu
Very good way to make vlookup understand.
Kindly give some lengthy sheet example and insert the cell name(contents) and after putting vlookup formula how to fill the colmns.
Chanchal
I understood your part I but now i am using your part II and in this post i am facing a problem actually when i am using vlookup advanced so i am trying to go through your example.After giving first name i want to give last name in the same sheet and same method but now it show #N/A………..
Pls tell me why it happened
siddhi
this is very useful because i dont know how to use v lookup now very sure to say i know vlookup.
thanks publisher
Hariharan Iyer
I have list of Airlines and percentage of discount offered by those airlines. I want the percentage of discount to be calculated on the other sheet on the base fare once i put the airline name and the base fare next to the airline. Pls suggest a way out.
Tracy
Excellent tutorial!! Thanks very much. This was much easier to follow than the instruction that Microsoft has published. I did have a little trouble with the VLookup converting Descriptions that started with a number. All of the text descriptions worked, but the numbers did not. I formatted the columns on both pages to be text, but that didn’t do much of anything. Just for future reference, any idea what I did wrong?
RENE
A very goodafternoon and thank you so much for posting such a wonderful insight on Excel spreadsheets. Sir, please can you explain more such stuffs, if so please where can i find it.
Robin
a 1 c 4
b 10 d 7
I want that if VLOOKUP in a cell (CELL F1) does not find or match with a or b, it will then go to c or d and put the value of that.
I was trying with this formula below:
=IF(VLOOKUP(F1,A1:B2,2)=FALSE,VLOOKUP(F1,D1:E2,2),VLOOKUP(F1,A1:B2,2))
But this is not giving the expected value.
Pls help me for solving this problem.
vasudha
thanku so much it is very useful for me…………………………
Thanku so much…
anand
thank you it’s really help me alot……….
ANIL MORWANI
i want help in pivot table, and also detailed v look up formula
Paulette Mudd
Very useful! Pivot table examples would be nice aslo. :) thanks
zorro
thanx
this is the very useful to my work
Lori
Thanks! You made vlookup easy to understand without having to read a whole manual. Very useful!
ajoy
i am very thankful this data for information given by me as known v lookup thankyou very much
Please Leave a Comment!