Apr 20 2007

VLookup in Excel

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


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 How-To 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.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

10 Comments on this post

Trackbacks

  1. 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
  2. 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
  3. 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 alpha-numeric – 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?
    Thanks

    January 14th, 2009 at 2:25 pm
  4. henryk said:

    It’s my first time here. An excellent site – learning something new everyday.
    Congratulations!!!

    July 8th, 2009 at 11:55 pm
  5. john said:

    Thank you Henryk! I’m glad you’re finding it useful. Please keep coming back.

    July 9th, 2009 at 12:19 am
  6. Dawood Mamedoff said:

    Hi, thanks for explanation! This tutorial also helped me much to understand how vlookup works:

    http://www.myhowtoos.com/en/excel-howtoos/84-how-to-match-values-in-excel-using-vlookup

    July 10th, 2009 at 10:10 am
  7. 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 again

    August 17th, 2009 at 4:22 pm
  8. 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
  9. john said:

    I haven’t written the post about this yet, but your answer can be found on this page I believe:

    http://www.techonthenet.com/excel/formulas/vlookup.php

    June 11th, 2010 at 10:24 am
  10. 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 Sub

    June 14th, 2010 at 5:06 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

  • Get Expert Excel Help Today! 714.262.6893

Categories

Excel Poll

How often do you use Excel at work?

View Results

Loading ... Loading ...

Tags

Comments Concatenate Contact Contest CountIf Ctrl-Shift-Enter Date Day Excel Excel 2010 Excel Formulas Excel Forum Excel Function Excel Info Excel Macro Excel Poll Features formula HLookup Hyperlink ISBLANK ISERROR ISNA Left Loan Payment Macro Macros microsoft excel Mid Month Rank Record Right Search Site Update Site Updates Substitute SumIf Today Userform VBA Visual Basic VLookup Wordpress Year