How to use VLOOKUP in Excel (part II) – some tips and advanced Excel tricks

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Last month, I wrote a short and simple example of 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 to get rid of the highlight around column G.

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 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:

=VLOOKUP(A2,Sheet1!A1:G8,3,FALSE)

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:

=VLOOKUP(A2,Sheet1!A1:G8,2,FALSE)

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:

=VLOOKUP($A2,Sheet1!$A$1:$G$8,3,FALSE)

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.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

99 Feedbacks on "How to use VLOOKUP in Excel (part II) – some tips and advanced Excel tricks"

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….



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.



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



Sathyanarayanan

its very useful



Romain

Thank you very much for your website, it is very usefull for me !

Try to continue like this.



GHANSHYAM CHAUHAN

IT IS A VERY USEFUL ME.

I GOD BLESS

THANKS



Ashwini Kabra

GR88888888888888888888888888888……….Thanks for such a valuable advice! keep it up……



Shelley

I’ve been working with excel for quite some time, but never quite understood how to do a vlookup, by the end of part 1 of the tutorial I was very knowlegable, and even challenged myself by creating my own example, and working with 2 worksheets. I also completed part 2 as well and now feel that I’m well on my way to becoming a vlookup expert. I was wondering if you could create a senario where you have to use the = “if(ISError(vlookup” function. I need to understand when to use that as well.



Ashutosh

superb… it is a nice work by u.. thanks alot. now i haveno doubt in excel. again thank you.



KARABO GUEVARA

Thanks for the greate tips and tricks, I learned a lot.

I have a question. Here is my question:

Is it possible to do a “ceasar cipher” of three over letters (input A and output is D)

(A=D, B=E, C=F, D=G, … X=A, Y=B, Z=C)
ABCDEFGHIJKLMNOPQRSTUVWXYZ
DEFGHIJKLMNOPQRSTUVWXYZABC

e.g. I LOVE CARS = L ORVH FDUV
e.g. WKDQN BRX = THANK YOU

Please help me, I am having difficulties in doing this in excel vlookup.



suman

Very useful . Thanks a lot .



Sahil Goyal

very good work..thank u for sharing such a useful thing…



Lokesh

Thnx alot…:)



Vishu

I’ve been facing a problem with this particular funtion.
Whenever I try to drag down the formula to other cells it shows #N/A because the formula gets distorted automatically for every cell. For e.g if for the first cell I have inserted the funtion as =vlookup(a2,sheet!a1:g8,3,false) then for the next cell right below it the copied formula becomes =vlookup(A3,sheet!A2:G9,3,false)

is there any better way to paste the formula as it is not feasible enter a new formula for every cell when you have 500 rows.

Plz help!



Dwan

Your page is not visible. I am using Crome as a Browser and you page looks like like someone smeared the contents with a paint brush.



Senthil Vijayan

Its useful for learning vlookup examples in excel. Thanks



Queen

Thank you very muc………h
Your tutorials are the best ever as did not see any as clear, easy to understand and straight forward like yours on the internet.



theju

good work , thanks a lot……..



Doug

Wow, thanks so much for the great tutorial. I was really struggling with VLOOKUP and this made it clear.



ABHINAV ANAND

AWESOME DEAR VERY HELPFUL TO ME.



riaz

dear sir,

This is the very useful information for excel job holders. so sir thank you twooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooomuch.



Ken

Thanks for the post..very helpful. I have a few related issues.
1. I am trying to create a drop down list (referencing another wookbook) this drop down list is a list of names, not all unique. I am having difficulty creating this list
2. once this drop down list is created and the user makes a selection I want to use vlookup to populate 2 separate cells one text one #’s (both in a separate worksheet)

any specific recommendations would be great. Thanks!



Rajee Bala

Big Thank you, I never thought VLookup can be so easy….. whenever I have to perform VLookup, I open this page religiously.



Ichcheghuri

thanks a lot.
its really worked for me.



Kamleshwar Singh Manral

It was a awesome example. By viewing this i boost my confidence in Vlookup. Appreciating work done…keep it up…



Vijay Sharma UnescoApptIPEXT

I was looking for something more advanced, as i’m currently a user of vlookup for multiple purposes. Nevertheless it was illustrated very well. Thnq a lot.



Lee

Brilliant job. Made life much easier!!



ivan

I appreciate your efforts as it is very well explained and easily understood. Only one thing i would suggest if it would be possible to have the sheet under demonstration to be fixed at one corner so as to save the effort of scrolling up and down.



Jasmine

Excellent tutorial. It helps me a lot. Thank u so much. BTW if cells contains dates then how to get the value in return in another sheet?



Laxmi

Wonderful post! It is very useful and more understandable. It helps me to learn vlookup easily.



Suresh M.R.

Very useful simple explanation. I want to send some more tables can i send. Please send your email id.



Rahul Pathak

Thanx a ton!!



HA

Just wanted to say thank you – just honing my memories of vlookups and formulae in advance of an interview. Thanks for a well written tutorial and for the little touches which have made me smile such as checking that I’m still ‘with you’ after more complex bits :o)
Hazel



Samya Bandyopadhyaya

Excellent work.Very helpful.Keep it up.Thanks



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.