VLookup in Excel
 14 Comment
The Vlookup Function is a part of Excel that I personally use everyday. So many Excel users have no idea it even exists, so I thought this would be a great place to start for ExcelHints.com. Once you learn how to use vlookup, it will greatly ease many of the pains you have had in trying to group two pieces of information together.
VLookup stands for Vertical Lookup. Basically what it does it looks up a value you tell it to, against another range of cells. If it finds a match, it returns the value in the same row as the match of the column you specify. You can either have it return results for exact matches or the closest match.
Let’s start by taking a look at the formula for VLookup:
VLookup(value, table_array, index_number, not_exact_match)
value is what you are searching for in the first column of the table_array
table_array is the range you would like to look up a value for. You must include both the column you are looking for a value in and the column of the value you would like to return
[ad#inpostad]
index_number is the column number in table_array from which the matching value is returned if a match is found. The first column in your table_array range is 1 (Must be positive integer greater than or equal to 1).
not_exact_match tells the formula if you are looking for an exact match based on value. Enter FALSE if you want it to find an exact match. Enter True to find an approximate match, in which Vlookup will look for the next largest value that is less than value (Must be sorted in ascending order for approximate match).
Possible Errors You May Receive
#REF – If the column you are indexing is greater than the number of column in you table_array range, you will receive this Error. Also if there is a problem with you table_array reference itself, this error could result
#N/A – If you were looking for an exact match that vlookup cannot find, it will return #N/A. Also if the approximate match cannot find and approximate match it will return #N/A as well
#Value – If you type in a value for an index that is less than 1, you will get this error.
Examples for Vlookup
Example 1: =vlookup(“Shirt”,A:D,2,false) will return “111″
Another way to to do example1 is to use a cell reference for value:
=vlookup(A2,A:D,2,false) will return “111″
Example 2: =vlookup(“Jacket”,A:D,2,false) will return “#N/A”
Example 3 : =vlookup(“Shirt”,A:D,5,false) will return “#REF”
Example 4: =vlookup(“Hat”,A:D,4,false) will return “4
VLookup can also reference other sheets or even other workbooks that you need to lookup a value on. All you will need is to include a reference to that worksheet or workbook in your formula. I have found that the easiest way is to highlight the range you want looked up with your mouse while you are typing the formula and Excel will automatically fill in the reference for you.
Use IF Statements and ISNA Function along with VLookup
I will be adding sections to the website on both IF Statements and ISNA to the site, but I personally believe using VLookup and these other functions go hand in hand because I use them together so frequently.
Many times when you are using VLookups for a large range of numbers, your result will come back #N/A but you may not want #N/A in your results. For example, you may want to have the formula return a 0 if the match is not found. To do this, you need to use all these functions together. Below is an example of how this could work for you (using the table from able):
Example 1: =IF(ISNA(VLOOKUP(“Jacket”,A:D,3,false)),”Not Found”,”Found”) will return “Not Found”
Example 2: =IF(ISNA(VLOOKUP(“Shirt”,A:D,3,false)),0,VLOOKUP(“Shirt”,A:D,3,false)) will return “Blue”
Features for Future Posts
One of the features that I will explore in a future post is the ability to lookup not just the first instance that the VLookup Function finds but he 2nd or 3rd Etc. I will have to post some other Function HowTo pages before I get into that though. Also using the Match() formula can be very useful to dynamically choose which row you want based on the criteria. We will explore Indexes and Array soon. Please use the comment section from my blog post to ask me any questions about this feature or give suggestions.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
Look for a new post on VLookups in 2015.
For now, see the YouTube video on Microsoft Excel VLookups by a Microsoft Excel MVP at Excel and Access, LLC.
More Excel Tips
14 Comments on this post
Trackbacks

Mark Oechsner said:
I have a question. I would like to look up a part number on a customer order. The left column is the customer order and in column 3 is the part number. A problem arrises when there are multiple part numbers on a Customer Order. How can I first look up the customer order, then with in that order, look up a specific part number? Thank you very much for your help.
Mark O.
February 26th, 2008 at 4:46 pm 
Lofu Martin said:
It is a fantastic thing to find a place to help a person to develop his or her in the field which is much interested for him. Actually, i would like to thank you for your support. In the last two week i visited your site and i got vital informationthat helpful to me. I just want you to sent to me more information about Excel and Access 2007. Your coopreation in doing so, will not be forgettable and thanks.
April 19th, 2008 at 10:49 am 
thumor said:
I am not able to use this function for a ‘simple’ need.
My Spreadsheet has this information:
Col A = Item Code, Col B = Size code,
Col C = Order Item Code, Col D = Order Qty.Item Codes can be alphanumeric – 1407, 1407A, 14077, etc.
so I formatted Col A and Col C as Text.I want to populate in Col E the size code for the Item on the Order.
Using Vlookup in Cell E2, I have =Vlookup(C2,A2:B300,2,FALSE)
But it does not work. I either get N/A# or a wrong Size code in Col E
Any suggestions / Advise?
ThanksJanuary 14th, 2009 at 2:25 pm 
henryk said:
It’s my first time here. An excellent site – learning something new everyday.
Congratulations!!!July 8th, 2009 at 11:55 pm 
john said:
Thank you Henryk! I’m glad you’re finding it useful. Please keep coming back.
July 9th, 2009 at 12:19 am 
Dawood Mamedoff said:
Hi, thanks for explanation! This tutorial also helped me much to understand how vlookup works:
http://www.myhowtoos.com/en/excelhowtoos/84howtomatchvaluesinexcelusingvlookup
July 10th, 2009 at 10:10 am 
Nicola said:
Hi,
Thanks for the great explanation of the vlookup function.
I was wondering if you think excel would be quicker at returning a vlookup result if it were numerical vs. alphanumerical ?I’m designing a vlookup that is based on a number of factors and wondered if using a lookup like 1_2_3 would be slower than if I looked up a large integer ?
Love the site !
Thanks againAugust 17th, 2009 at 4:22 pm 
Aldo said:
I am looking for the link to the function explanation “VLookup Function finds but he 2nd or 3rd Etc”.
Was it eer published?June 11th, 2010 at 9:32 am 
john said:
I haven’t written the post about this yet, but your answer can be found on this page I believe:
June 11th, 2010 at 10:24 am 
cla said:
hello,
i have a little prob on my excel macro that use vlookup. my data column (F2) will be added or decrese daily. the problem is, i make an excel macro, but there are still empty column after refresh vlookup done bcz of different data column (F2) every single of day. fyi, my vlookup table (G2) is still maintain, only the refreshed data list (F2) increase or decrese daily. can anybody help.here i paste the VB:
Range(“F2″).Select
ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[2],Sheet1!RC[4]:R[1294]C[3],2,FALSE)”
ActiveWindow.SmallScroll Down:=15
ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[2],Sheet1!R2C2:R1296C3,2,FALSE)”Range(“G2″).Select
ActiveCell.FormulaR1C1 = “=EXACT(RC[2],RC[1])”
Range(“F2:G2″).Select
Selection.AutoFill Destination:=Range(“F2:G1896″)
Range(“F2:G1896″).Select
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 289
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 333
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 362
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 423
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 452
ActiveWindow.ScrollRow = 466
ActiveWindow.ScrollRow = 500
ActiveWindow.ScrollRow = 534
ActiveWindow.ScrollRow = 568
ActiveWindow.ScrollRow = 583
ActiveWindow.ScrollRow = 597
ActiveWindow.ScrollRow = 612
ActiveWindow.ScrollRow = 626
ActiveWindow.ScrollRow = 641
ActiveWindow.ScrollRow = 655
ActiveWindow.ScrollRow = 667
ActiveWindow.ScrollRow = 680
ActiveWindow.ScrollRow = 692
ActiveWindow.ScrollRow = 704
ActiveWindow.ScrollRow = 718
ActiveWindow.ScrollRow = 730
ActiveWindow.ScrollRow = 745
ActiveWindow.ScrollRow = 760
ActiveWindow.ScrollRow = 774
ActiveWindow.ScrollRow = 789
ActiveWindow.ScrollRow = 803
ActiveWindow.ScrollRow = 815
ActiveWindow.ScrollRow = 827
ActiveWindow.ScrollRow = 842
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 869
ActiveWindow.ScrollRow = 881
ActiveWindow.ScrollRow = 893
ActiveWindow.ScrollRow = 907
ActiveWindow.ScrollRow = 922
ActiveWindow.ScrollRow = 932
ActiveWindow.ScrollRow = 944
ActiveWindow.ScrollRow = 956
ActiveWindow.ScrollRow = 965
ActiveWindow.ScrollRow = 978
ActiveWindow.ScrollRow = 990
ActiveWindow.ScrollRow = 997
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 1016
ActiveWindow.ScrollRow = 1026
ActiveWindow.ScrollRow = 1038
ActiveWindow.ScrollRow = 1048
ActiveWindow.ScrollRow = 1058
ActiveWindow.ScrollRow = 1067
ActiveWindow.ScrollRow = 1077
ActiveWindow.ScrollRow = 1089
ActiveWindow.ScrollRow = 1099
ActiveWindow.ScrollRow = 1113
ActiveWindow.ScrollRow = 1128
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1155
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1184
ActiveWindow.ScrollRow = 1218
ActiveWindow.ScrollRow = 1232
ActiveWindow.ScrollRow = 1247
ActiveWindow.ScrollRow = 1261
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1285
ActiveWindow.ScrollRow = 1300
ActiveWindow.ScrollRow = 1310
ActiveWindow.ScrollRow = 1322
ActiveWindow.ScrollRow = 1334
ActiveWindow.ScrollRow = 1348
ActiveWindow.ScrollRow = 1360
ActiveWindow.ScrollRow = 1370
ActiveWindow.ScrollRow = 1382
ActiveWindow.ScrollRow = 1390
ActiveWindow.ScrollRow = 1402
ActiveWindow.ScrollRow = 1411
ActiveWindow.ScrollRow = 1421
ActiveWindow.ScrollRow = 1431
ActiveWindow.ScrollRow = 1438
ActiveWindow.ScrollRow = 1450
ActiveWindow.ScrollRow = 1460
ActiveWindow.ScrollRow = 1467
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 1484
ActiveWindow.ScrollRow = 1487
ActiveWindow.ScrollRow = 1491
ActiveWindow.ScrollRow = 1496
ActiveWindow.ScrollRow = 1499
ActiveWindow.ScrollRow = 1503
ActiveWindow.ScrollRow = 1508
ActiveWindow.ScrollRow = 1513
ActiveWindow.ScrollRow = 1520
ActiveWindow.ScrollRow = 1525
ActiveWindow.ScrollRow = 1533
ActiveWindow.ScrollRow = 1545
ActiveWindow.ScrollRow = 1554
ActiveWindow.ScrollRow = 1566
ActiveWindow.ScrollRow = 1579
ActiveWindow.ScrollRow = 1591
ActiveWindow.ScrollRow = 1598
ActiveWindow.ScrollRow = 1605
ActiveWindow.ScrollRow = 1608
ActiveWindow.ScrollRow = 1613
ActiveWindow.ScrollRow = 1617
ActiveWindow.ScrollRow = 1622
ActiveWindow.ScrollRow = 1625
ActiveWindow.ScrollRow = 1629
ActiveWindow.ScrollRow = 1632
ActiveWindow.ScrollRow = 1634
ActiveWindow.ScrollRow = 1637
ActiveWindow.ScrollRow = 1642
ActiveWindow.ScrollRow = 1644
ActiveWindow.ScrollRow = 1646
ActiveWindow.ScrollRow = 1651
ActiveWindow.ScrollRow = 1656
ActiveWindow.ScrollRow = 1659
ActiveWindow.ScrollRow = 1666
ActiveWindow.ScrollRow = 1671
ActiveWindow.ScrollRow = 1678
ActiveWindow.ScrollRow = 1685
ActiveWindow.ScrollRow = 1692
ActiveWindow.ScrollRow = 1697
ActiveWindow.ScrollRow = 1705
ActiveWindow.ScrollRow = 1709
ActiveWindow.ScrollRow = 1714
ActiveWindow.ScrollRow = 1719
ActiveWindow.ScrollRow = 1724
ActiveWindow.ScrollRow = 1729
ActiveWindow.ScrollRow = 1734
ActiveWindow.ScrollRow = 1739
ActiveWindow.ScrollRow = 1743
ActiveWindow.ScrollRow = 1746
ActiveWindow.ScrollRow = 1751
ActiveWindow.ScrollRow = 1753
ActiveWindow.ScrollRow = 1755
ActiveWindow.ScrollRow = 1758
ActiveWindow.ScrollRow = 1760
ActiveWindow.ScrollRow = 1763
ActiveWindow.ScrollRow = 1765
ActiveWindow.ScrollRow = 1768
ActiveWindow.ScrollRow = 1770
ActiveWindow.ScrollRow = 1772
ActiveWindow.ScrollRow = 1777
ActiveWindow.ScrollRow = 1782
ActiveWindow.ScrollRow = 1785
ActiveWindow.ScrollRow = 1787
ActiveWindow.ScrollRow = 1789
ActiveWindow.ScrollRow = 1792
ActiveWindow.ScrollRow = 1794
ActiveWindow.ScrollRow = 1797
ActiveWindow.ScrollRow = 1799
ActiveWindow.ScrollRow = 1804
ActiveWindow.ScrollRow = 1809
ActiveWindow.ScrollRow = 1814
ActiveWindow.ScrollRow = 1818
ActiveWindow.ScrollRow = 1823
ActiveWindow.ScrollRow = 1828
ActiveWindow.ScrollRow = 1833
ActiveWindow.ScrollRow = 1838
ActiveWindow.ScrollRow = 1840
ActiveWindow.ScrollRow = 1843
ActiveWindow.ScrollRow = 1848
ActiveWindow.ScrollRow = 1850
ActiveWindow.ScrollRow = 1823
ActiveWindow.ScrollRow = 1789
ActiveWindow.ScrollRow = 1746
ActiveWindow.ScrollRow = 1702
ActiveWindow.ScrollRow = 1663
ActiveWindow.ScrollRow = 1620
ActiveWindow.ScrollRow = 1576
ActiveWindow.ScrollRow = 1537
ActiveWindow.ScrollRow = 1494
ActiveWindow.ScrollRow = 1450
ActiveWindow.ScrollRow = 1407
ActiveWindow.ScrollRow = 1363
ActiveWindow.ScrollRow = 1310
ActiveWindow.ScrollRow = 1261
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1072
ActiveWindow.ScrollRow = 995
ActiveWindow.ScrollRow = 917
ActiveWindow.ScrollRow = 844
ActiveWindow.ScrollRow = 776
ActiveWindow.ScrollRow = 709
ActiveWindow.ScrollRow = 650
ActiveWindow.ScrollRow = 587
ActiveWindow.ScrollRow = 529
ActiveWindow.ScrollRow = 461
ActiveWindow.ScrollRow = 394
ActiveWindow.ScrollRow = 326
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 1
Range(“K5″).Select
End SubJune 14th, 2010 at 5:06 am 
Mohammad said:
Dear
i need help. i am trying to match two columns and then return another column value if both are matching.
how i could do it?
thxApril 25th, 2012 at 6:49 pm 
anil said:
its understandable
June 5th, 2012 at 9:49 am 
chandrakant said:
I want more details of vlookup formula.Pls help me.
January 8th, 2013 at 7:03 am
[…] of several upcoming blog posts we are writing as a followup to the original post John did on the Microsoft VLookup Function way back in […]