Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Understanding the VLOOKUP Function.

Understanding the VLOOKUP Function

Written by Allen Wyatt (last updated August 17, 2020)
This tip applies to Excel 2007 and 2010


35

Excel has a huge number of different worksheet functions—some of them used quite often, and others quite obscure. One of the more obscure functions (at least for Excel novices) is the VLOOKUP function. Understanding how this function works can make your life much simpler, especially when dealing with tables of data and pulling information from those tables.

VLOOKUP is a shortened form of "Vertical Lookup." It is a function that looks vertically (up and down) through a data table and extracts information from the table as you direct. For instance, let's assume you have a data table that lists part numbers and their prices. The part numbers are in column H, and the prices for those parts are in column I. (Assume the data table is in the range H5:I27.) You can use the following formula to look up a part number (located in cell C28) and return its price:

=VLOOKUP(C28,H5:I27,2)

Notice that, at a minimum, VLOOKUP requires three arguments. The first is the value that you want to look up. In this case, C28 contains the part number to be matched in the data table. The second argument is the actual data table, in this case H5:I27. The third argument specifies from which column of the table the value should be returned. In this case I wanted the price, which was in the second column (column I) of the data table.

What VLOOKUP does, in this instance, is to take the value in C28 and then try to match it to a cell in the first column of the range H5:127. If it finds a match, then it returns the value from column 2 of that range—the price we wanted.

VLOOKUP will also accept an optional fourth argument, which can be either TRUE or FALSE. The default value for the argument is TRUE, which means that VLOOKUP will approximate values when matching them in the data table. If an exact match cannot be found in the data table, then VLOOKUP considers the next lowest value in the first column of the table to be a match. Thus, if you are looking for a part number such as "P23," and there is no such part number in the table, but there is a "P22," then VLOOKUP considers that a match. If you set the optional fourth argument to FALSE, then VLOOKUP only returns successfully if it can make an exact match.

Because of the way in which VLOOKUP does its matching, it is very important that the information in your data table be sorted in ascending order according to the values in the first column.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9576) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Understanding the VLOOKUP Function.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Only Inline Figures Can be Seen and Printed

Insert a graphic into a document and you expect to be able to see it. What do you do if it isn't displayed, however? Here ...

Discover More

Setting Maximum Line Lengths in Word E-mail Messages

When you use Word as your e-mail editor, it allows you to format the text of your e-mail messages using tools you are ...

Discover More

Adding Page Numbers in Headers or Footers

While Word has a default format for page numbers, you can design and specify how you want them to appear in your ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Using COUNTIF with Colors

Excel allows you to easily format cells with different fonts, borders, and colors. If you want to count the number of ...

Discover More

Returning Blanks or Asterisks from a Lookup

Want to return more than a value when doing a lookup? Here are a couple of ways to do it by adding an IF clause to your ...

Discover More

Indirect References to a DSUM Parameter

Indirect references can be very helpful in formulas, but getting your head around how they work can sometimes be ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 3?

2020-08-17 11:54:15

David Czuba

Microsoft should remove the requirement that the list should be in sorted order, because users who update table data, say, with new part numbers, typically add the data to the end of the table, but do not re-sort the data. This makes VLOOKUP unreliable. The app should sort the data internally before doing the lookup, and not depend on user proficiency.


2020-08-17 07:23:31

Philip

Probably good to publish some more information about the new XLOOKUP function ?


2015-08-10 00:24:14

kiran kumar

how to vlookup email id's sql database .xml file, and how to remove duplicates (both duplicates)...


2015-05-02 21:04:53

TJ

A toughie(?). I have a spreadsheet with COL A with Award1-Award100 (A2:A101). COL B has students from a dropdown list categorized by grade level in elementary school for each award, and by grade and homeroom in middle school (contiguous except for fill-in breaks denoting grade level change ex. Sixth grade Jones, then after 22 students Sixth grade Kyle, etc.). Column C is the award dropdown list; Col D is grade level, manual entry; Col E is dropdown for Stage Award or Classroom Award; COL F is Teacher giving the Award - manual entry; all these COLUMNS are good to go and work fine. Here's the challenge: COL G needs to populate with a specific Homeroom teacher when a student's name is chosen from COL B. I have the source data in a hidden sheet (COL K for any who answer this). Example: If someone in the Smith homeroom is chosen to receive an award, then COL G will show Smith as the homeroom teacher. Once this spreadsheet is finished, the teachers send it to me and I concatenate the sheets or send them singly to the school Principal. He wants the homeroom teacher to populate automatically and the teachers are expecting the worksheet May 4th to start working on it.

Call the first worksheet Awards, and the hidden data sheet Source. Thanks. I love working in excel, but I do a lot more than spreadsheets in my job as Teacher and Campus Technologist.


2014-01-22 01:54:32

Shreepad S M Gandhi

Got an error while posting comment 2 of 2 so I had to further break down my post. This is a continuation to my Comment 2 of 2.
.
If you type 1000 in Cell C1 the output is
India, Wonderful,
If you type 2000 in Cell C1 the output is
Japan, Rising Sun,
If you type 3000 in Cell C1 the output is
America, Hollywood.

The macro is not written by me. I got it at some discussion forum and I credit his due contribution. But I exclusively used this in my assignments without encountering any major problems so far. Thanks.


2014-01-22 01:52:56

Shreepad S M Gandhi

Comment 2 of 2
.
---------------------------------------
On the Excel Sheet1,
Let in Column A,

Cell A1 = 1000
Cell A2 = 2000
Cell A3 = 3000
Cell A4 = 3000
Cell A5 = 1000
Cell A6 = 2000

Let in Column B,

Cell B1 = "India"
Cell B2 = "Japan"
Cell B3 = "America"
Cell B4 = "Hollywood"
Cell B5 = "Wonderful"
Cell B6 = "Rising Sun"

Let Cell C1 be the reference cell

Key in the formula below in any other cell (say D1)

"=FindSeries(A1:A6,C1)"


2014-01-22 01:51:52

Shreepad S M Gandhi

Comment 1 of 2

This is a lookalike of 'vlookup' function.
It displays contents of all cells (seperated by comma) in a column corresponding to similar values of a previous column. Try this example.

Open a new Excel workbook.

In a module in View Code type the code below:
----------------------------------------
Public Function FindSeries(TRange As Range, MatchWith As String)

For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", " & Chr(10)
End If
Next cell
FindSeries = Left(x, (Len(x) - 2))
End Function

'Note: "Chr(10)" above inserts a line feed in the output


2014-01-16 11:05:46

Scott Renz

Hi Nukecity,

I meant the curly braces around the {1,2}, not around the whole formula. I did not enter the outer curly braces around the whole formula and it worked.


2014-01-16 11:03:34

Scott Renz

Hi Nukecity,

I did not enter it as an array formula and it worked just fine. I just entered it with the curly braces as a regular formula and it worked for me.


2014-01-15 08:17:29

nukecity

Norm's tip combining VLOOKUP and CHOOSE deserves one comment: the formula needs to be entered as an array formula - e.g. ={VLOOKUP(B24,CHOOSE({1,2},$E$25:$E$30,$C$25:$C$30),2,FALSE)}.


2014-01-11 20:57:06

Tony

I've been using VLOOKUP for the last 18 months, to merge information from two different monthly reports on a range of products, since our accounting program doesn't give both together.


2014-01-11 05:28:45

Michael (Micky) Avidan

@Debbie,
I hope the linked picture helps you.
http://jpg.co.il/download/52d11c34815a2.png
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)
ISRAEL


2014-01-10 12:51:28

Chuck Trese

Debbie,
Back to your original question about using VLOOKUP, and can you "flag" that the data table actually has duplicates. An esy way to simply know IF there are duplicates (but not identify their location for you) is to use the COUNTIF function.
Say your vin #'s are in A2:A2000, and the vin # you are searching for was entered in F2.
=COUNTIF(A2:A2000,F2) will tell you how many matches are found.
=IF(COUNTIF(A2:A2000,F2)>1,"duplicates","unique") will tell you in words.
to make it really jump out at you, you can use conditional formatting on the cell containing this formula - maybe tell it to use red font and bold text if it contains the word "duplicates".


2014-01-10 12:30:21

Chuck Trese

Debbie,
to find duplicates:
1) select range (cells containing vin #'s)
2) on Home tab, select Conditional Formatting > New Rule > "Format only unique or duplicate values" 5th option.
3) make sure drop-down box shows "duplicate" (not "unique")
4) then, in this same dialog box, select Format > Fill > select your color (let's say orange)
5) click OK
6) click OK
Now, all your duplicates should be colored orange.
Using autofilter, select "Filter by Color" > choose orange.
Now your list will be filtered to show only those vin #'s with duplicate entries in the range you selected in step 1.
To go back to the full list, use autofilter to "Clear Filter from [your-column-header]"


2014-01-10 08:43:25

Debbie A.

Some more info about my previous question.
I have a list of vehicle id #'s from the factory. They sort their lists by the last 6 of the vin#. They also list separate sections where the same vin may be in multiple sections. I also find that the same last 6 digits may belong to two different vehicles.
I start with this big mass list of vins and would like to know if the same last 6 digits in the column appear more than once in the long list. How do I do this? I can't do a find for each of the vin's in the report. It would be faster to sort it and just look down the list for dups and then put it back into original sort format.


2014-01-09 13:18:45

Dan G

I too use Index/Match instead, you just have to make sure the start rows of both arrays are the same.

=index(i5:i27,match(c28,h5:28,0))

Not only do I not have to count columns, but I can get data that is located in a column to the left of the column I am searching in. You can also do the match in a separate column then use that to do find multiple pieces of data from the same line. For instance I could do a match on a customer number. Then reference that match to get the customer name and address. It is also much faster to do a single match then to do multiple lookups.

After switching to Index/match I haven't seen a reason to use Vlookup, am I missing something?


2014-01-08 17:25:31

Bob D

Used H/Vlookup for many years from Excel 5 onwards. However the lookup function is a memory hog and can be very cumbersome when used in very large data base environments. Combining Match and Index functions is far more exacting fro results and flexible in manipulation and does not make unreasonable demands on resources. Sorting is not necessary with M&I. Giving your database a range name is also recommended and the data itself should always be contiguous.


2014-01-08 13:24:16

Mathew

Vlookup and SumIf(s) without a doubt some of the most useful functions in excel.

Although I almost always use vlookup with false.


2014-01-08 12:22:44

Scott Renz

As to Norm's comment, I found the following on myOnlineTraininghub:

Now, on its own, like the example above, CHOOSE is not much use but when you use it in a VLOOKUP it enables us to trick Excel into returning the value to the left of our lookup column.

Our formula to look up date 29th January 2011 in column K and return the value in column E (column number 2) is:

=VLOOKUP(DATE(2011,1,29),CHOOSE({1,2},$K$2:$K$207,$E$2:$E$207),2,0)Translated:

=VLOOKUP(find 29/01/2011 in column K and return the value in column E)


2014-01-08 12:01:08

marco

I've always preferred Index-Matching, but only because I'm too lazy to count columns:

=INDEX(A:A,MATCH(B:B,C:C,0)

where Col A contains the value you're looking for, and Col B & C have the values you're comparing to one another. The zero makes it look for exact matches.


2014-01-08 11:45:51

Neil

Norm- I'm intrigued; how is the {1,2} arguement interpreted by the CHOOSE function? Will it find a match in column E and return the result from column D? Was there supposed to be a link to an example? Thanks.


2014-01-08 11:00:38

Char

I use it with job numbers. In my spreads I enter the job number and I get a return of the Customer and Client. It keeps the information the exact same for all entries for that specific job number. I use VLOOKUP all the time for similar needs.


2014-01-08 10:44:47

Kathy B

Suzy,

Try using conditional formatting to find duplicates. It works great.


2014-01-08 10:43:00

Kathy B

Debbie:

You may want to use conditional formatting to highlight those items in your spreadsheet that match that six digit code rather that using VLOOKUP.


2014-01-08 09:42:48

Chuck Trese

Oops, sorry, Debbie.


2014-01-08 09:41:09

Chuck Trese

Debra,
You might want to read the help for the COUNTIF function. This may be helpful to you, as it can tell you how many cells in a range fit your criteria.


2014-01-08 08:35:12

Debbie A.

I use VLOOKUP a lot to match a 6 digit string in an ID number. Occasionally I have the same 6 digit string listed for 2 different items. Is there any way to have it flag this or notify me? I find these by lots of research and it is time consuming in a huge table.


2014-01-08 08:17:56

Jeff

I learned this a long time ago. I have since shown a lot of people who still to this day thank me for such a time saving tool. In the past they manually did what VLOOKUP does automatically.


2014-01-08 08:17:53

Norm

My favorite VLOOKUP formula is when you combine VLOOKUP with the CHOOSE function.
Which allows you see search to the left

=VLOOKUP(B24,CHOOSE({1,2},$E$25:$E$30,$C$25:$C$30),2,FALSE)

SEE EXAMPLE


2013-07-13 19:38:00

Mark Watkin

Found this useful -

http://officeimg.vo.msecnd.net/en-gb/files/818/530/AF101984660.pdf


2013-06-21 15:31:09

Aldo

Vlookup() and Hlookup() are basic database get functions. They look for a specific record and return as result.

There are database functions available to find totals. Look at the D-functions like DGet(), DSum(), DCount(), etc.

To find duplicates using conditional formating.


2013-06-13 05:29:46

pawan roka

how can i use the vlookup to find total ?


2013-05-29 17:39:40

Suzy

How can I use the v lookup function to find duplicates? That is, I want to find all the occurences in Col B of each cell from Col A...
Thanks!


2012-10-02 12:29:34

Chuck M.

The last sentence about keeping the table sorted in ascending sequence does not apply if you use the "FALSE" optional parameter, meaning, I want exact matches. In my case, I needed to keep the table sorted in a different sequence.


2012-05-26 21:36:55

Tony

The easiest and best explanation of VLookup


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.