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!
VLOOKUP is an oft-used worksheet function to lookup values in a data table. If the function cannot return a value, it ...
Discover MoreNormally the VLOOKUP function returns a value, and if it can't return a value it returns a zero. Here's how you can use ...
Discover MoreFinding a square root is easy because Excel provides a worksheet function for that purpose. Finding a different root may ...
Discover MoreFREE 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
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
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
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
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
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
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
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
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
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