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

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

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

When working with finances, you often need to know the rate of return on a given investment. The most common type of ...

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

Discover MoreIf you need to insert into a cell the name of the workbook in which a worksheet is contained, you can use the CELL ...

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

2020-08-17 11:54:15

David Czuba

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

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

.

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

.

---------------------------------------

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

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

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

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

2014-01-11 20:57:06

Tony

2014-01-11 05:28:45

Michael (Micky) Avidan

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

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

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.

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

=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

2014-01-08 13:24:16

Mathew

Although I almost always use vlookup with false.

2014-01-08 12:22:44

Scott Renz

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

=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

2014-01-08 11:00:38

Char

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

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

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.

2014-01-08 08:17:56

Jeff

2014-01-08 08:17:53

Norm

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

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

Thanks!

2012-10-02 12:29:34

Chuck M.

2012-05-26 21:36:55

Tony

The easiest and best explanation of VLookup

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.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments