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

Excel function 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


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

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

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



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



Comments

Please Leave a Comment!





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