• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel VLOOKUP Formulas Explained

You are here: Home / Excel Formulas / Excel VLOOKUP Formulas Explained
vlookup formula
September 20, 2010 by Mynda Treacy

The Excel VLOOKUP formula is my favourite! Perhaps because it was one of the first formulas I mastered. It gave me an insight into the power of Excel and how it could help me in my job.

Interestingly, there are two ways you can use the VLOOKUP function; exact match and approximate match. However, I find that most people know one way or the other, and only a few know both.

Note: if you have Microsoft 365 you should use the XLOOKUP function, which can do everything VLOOKUP can do and more. Plus, it's not susceptible to the same errors VLOOKUP is.

Excel VLOOKUP Formula Video Tutorial

Subscribe YouTube

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel workbook used in this example so you can practice to make sure you really get it and can take advantage of its power. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Excel VLOOKUP Formula Written Example

Using the example data below, I want to lookup the commission rate in column I of the table that's in columns H:J, for the salesperson listed in column C, and put the result in column E.

Excel Vlookup Formula table

Excel VLOOKUP Function Syntax

Before we get started, let's understand the VLOOKUP function's syntax.

=VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

And to translate it into English it would read:

=VLOOKUP(find this value, in that table, return the value in the nth column of the table, but only return a result if you can match the value exactly*)

* for an exact match you must specify FALSE or 0 in the last argument of the VLOOKUP formula.

Let’s make it even clearer by applying it to our example:

=VLOOKUP(find the name Doug from cell C17, in the Commission Rates table H17:J24, return the value in column 2 of the table,  but only return a value if you find the exact name "Doug" in the Commission Rates table, otherwise give me an error)

calculate commission with Excel VLOOKUP Formula

Rules, Common Mistakes and Troubleshooting!

  1. ‘Return the value in column 2 of the table’ is referring to the column number in the table H17:J24, not the column number of the spreadsheet. The information we want returned is the percentage rate, and it is in the second column of the Commission Rates table.
  2.  
  3. ‘Only return a result if you can match the value exactly’ is telling Excel that we only want information returned if it matches our criteria exactly. i.e. Find Doug in our Commission Rates Table, and if you can’t find Doug, give me an error. The error displayed will be #N/A.
  4.  
  5. VLOOKUP formulas read from left to right. You must have the information you are looking up (in our example the salesperson's name), in the first column of the lookup_array range.
  6.  
  7. Lookup Table Location: The ‘Table’ you are looking up can be in the same spreadsheet. Or a different sheet in the same workbook. Or in a different workbook altogether.
  8.  
  9. Sort Order: The table doesn’t have to be sorted in any particular order when using the Exact Match version of the formula, but you must not have duplicates. Unless the information on each duplicate is exactly the same.  For example, if Doug appeared twice in our Commission Rates table with different percentage rates for each instance, VLOOKUP would return the rate on the first instance of Doug.
  10.  
  11. VLOOKUP isn't case sensitive, so 'Doug' could be 'doug' or 'DOUG' or 'Doug', in either column C or the Commission Rates table.
  12.  
  13. When VLOOKUP formulas return #N/A errors it means Excel can't find the value you're trying to look up in your table. If you get this, but you can ‘plain as day’ see it's there in the table, then it’s likely you’ve got one of the values prefixed with an apostrophe. To check this go to each cell you're referencing and look in the formula bar and see if there is an apostrophe in either cell.  You can only see the apostrophe from the formula bar. See example below.

    Text formatting in formula bar

    Text Formats: Excel reads text prefixed with an apostrophe different to text without. Even though on the face of the spreadsheet they might look the same. You need to make sure both the value you're looking up, and the value in the table either both have the apostrophe, or both don't. The quickest way to get rid of the apostrophes is to do ‘Text to Columns’. Or run it through the VALUE function, which converts numbers formatted as text to actual numbers.
  14.  
  15. Leading or Trailing spaces are another cause of VLOOKUP errors. Edit the cells to check if there are extra spaces before or after the lookup value, or the data in the first column of the lookup_array. Use the TRIM function to easily clean leading and trailing spaces.
  16.  
  17. Dynamic Column References: Use COLUMNS or the MATCH function to dynamically find the col_index_num argument for VLOOKUP. See the video above for step by step instructions.

Excel VLOOKUP Approximate Match Formula

Now you've mastered the VLOOKUP exact match formula, you're ready to learn VLOOKUP approximate match. This technique is lesser known and many people make the mistake of using a nested IF formula instead of this simple approach.

vlookup formula

More Vlookup Posts

vlookup in power query using list functions

VLOOKUP in Power Query Using List Functions

Use List functions to do VLOOKUPS in Power Query as an alternative to merging tables. Sample data and file available to download
VLOOKUP Multiple Values in Multiple Columns

VLOOKUP Multiple Values in Multiple Columns

Excel VLOOKUP Multiple Sheets

Excel VLOOKUP Multiple Sheets

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

VLOOKUP Multiple Criteria

VLOOKUP Multiple Criteria

Excel VLOOKUP Multiple Values

Excel VLOOKUP Multiple Values

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel VLOOKUP with Dynamic Column Reference

Excel VLOOKUP with Dynamic Column Reference

Excel VLOOKUP with Dynamic Column Reference allows you to copy the formula across columns and have the column reference automatically update.
Excel VLOOKUP to the Left Using CHOOSE

Excel VLOOKUP to the Left Using CHOOSE

VLOOKUP to the left with this clever use of CHOOSE to trick Excel into looking up a column to the left.

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel FormulasTag: vlookup
Previous Post:Excel COUNTIF and COUNTIFS Formulas Explained
Next Post:Excel VLOOKUP – Sorted List Explainedvlookup sorted list formula

Reader Interactions

Comments

  1. MIRACLE

    October 3, 2022 at 8:06 pm

    Good morning from Nigeria i will like the pdf explanation on on all the lookups i will be glad if my request is granted thanks God Bless.

    Reply
    • Mynda Treacy

      October 4, 2022 at 9:08 am

      There’s not enough information. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
    • Philip Treacy

      October 4, 2022 at 9:15 am

      Hi,

      I’m afraid we don’t have any PDF explanations for the lookup functions. But you can search our blog for the term lookup and you’ll find lots of posts.

      Or you can take our Advanced Excel Formulas course

      Regards

      Phil

      Reply
  2. Sudha

    September 14, 2022 at 4:27 am

    INDEX where in the row MATCH Sheet1 D7(date) in Sheet2 E:E and match Sheet1 F1 in any one cell Sheet2 B2 to U2 (sheet 1 F$1 equal to all the cells in Sheet2 from B2 to U2-any code number), and match Sheet1 G$1(text) in only one cell in Sheet 2 B4 To U4. next Sheet1 D8 for entire column

    Reply
    • Mynda Treacy

      September 14, 2022 at 8:30 am

      Hi Sudha, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  3. Ruby

    June 27, 2022 at 4:55 pm

    How do i use vlookup formula to below
    Agent ID Remaining Leaves Bonus
    2345 6 2000
    5457 4 3000
    9823 7 4000
    1233 2 3000
    2344 8 5000

    You’ve been asked to come up with a way to check the bonus of the agent when the Agent ID is typed into a given cell

    Agent ID 2344
    Bonus

    Reply
    • Mynda Treacy

      June 27, 2022 at 6:48 pm

      Hi Ruby,

      This looks like a test question. There is some information missing, namely how the bonus is calculated. However, you might find this VLOOKUP on a sorted list helpful.

      Mynda

      Reply
  4. A Kaur

    March 5, 2022 at 1:11 pm

    I am trying to use this =VLOOKUP($A4,Data2!$A:$AC,H$1,FALSE) to pull forecast for multiple months from data file but this does seems to be working, could you please walk me through to use this formula appropriately ?

    Reply
    • Mynda Treacy

      March 5, 2022 at 1:46 pm

      There’s nothing obvious wrong with your formula, so it must be an issue with the data in the cells being referenced. You can post your question on our Excel forum where you can also upload a sample file and we can help you further

      Reply
  5. Gabriel J Zanche

    September 30, 2021 at 11:28 pm

    How do I set up a formula for removing a list of names in sheet 1 from a larger list of names in list 2 and thus, have fewer names in list 2 with none of the names as per list 1?

    Reply
    • Mynda Treacy

      October 1, 2021 at 9:00 am

      Hi Gabriel, you can use Power Query to compare the lists.

      Reply
  6. Lauren

    March 20, 2021 at 1:47 am

    After struggling with VLOOKUPS for days, this has been the clearest explanation I’ve seen of how they work. After reading this guide (especially the part where you applied it to your example), I was able to do my VLOOKUPS in no time. Thank you!

    Reply
    • Mynda Treacy

      March 21, 2021 at 10:46 am

      So pleased I could help, Lauren!

      Reply
  7. kamal goswami

    June 19, 2020 at 4:09 pm

    this is description also available in another language ? like us (hindi)

    Reply
    • Mynda Treacy

      June 19, 2020 at 4:22 pm

      Sorry, Kamal, we don’t have it in other languages.

      Reply
  8. johnlloyd

    May 12, 2020 at 11:32 am

    its ok thanks for you

    Reply
  9. Harriman

    February 2, 2020 at 10:40 am

    Please help me understand each of the arguments in this lookup formula:

    =LOOKUP(1,1/($A$5:$I$5″”),$A$5:$I$5)

    What does the “/” character do?
    Changing the number in the first argument doesn’t seem to effect the result. Why?

    I can utilize the formula but would like to understand it.

    Reply
    • Philip Treacy

      February 2, 2020 at 12:18 pm

      Hi,

      That is not a valid formula. Please see LOOKUP Function

      The set of double quotes should not be there and you are using the same range for the lookup vector and the result vector. Something like this would work

      =LOOKUP(1,1/($A$5:$I$5),$A$6:$I$6)

      Where

      A5:I5 is 1, 2, 3, 4, 5, 6, 7, 8

      and

      A6:I6 is a, b, c, d, e, f, g, h, i

      The input vector is therefore 1/$A$5:$I$5 i.e. 1/2, 1/3, 1/4, 1/5 etc

      Changing the first argument (the lookup value) can affect the result but it will depend on what your input vector and result vector values are. If the first argument is not found, the result is the smallest value nearest the lookup value. Unless the lookup value is smaller than the smallest value in the lookup vector, in which case you’ll get an error.

      Regards

      Phil

      Reply
      • Harriman

        February 3, 2020 at 2:28 pm

        Phil, Thank you for your prompt reply. There was a typo in the formula I presented.
        The goal was to lookup the last non-blank entry in a designated range and then return the corresponding entry in a second designated range.

        My corrected formula should read:

        =LOOKUP(1,1/(A8:I8<>""),A14:I14) This worked for me but I wanted to understand the arguments.

        It seems to count over to the last non-blank entry in row 8 and then returns the corresponding entry in the (same column) second designated range. (in this formula (A14:I14). If the “” expression is omitted, the result vector is offset by – I wish to understand exactly what each argument does – specifically, the first and second arguments and the “/” character and how to offset the result vector.

        Your assistance is much appreciated.

        Reply
        • Philip Treacy

          February 3, 2020 at 10:55 pm

          Hi Harriman,

          To check for the last non-blank entry you need to use 2 as your lookup value i.e.

          =LOOKUP(2,1/(A8:I8<>""),A14:I14)

          Let’s say A8:I8 contains a, b, c, d, e, f, g, h, i

          and A14:I14 contains 1, 2, 3, 4, 5, 6, 7, 8, 9

          (A8:I8<>"") gives an input vector of True or False values depending on whether or not the cell contains a value or to be more specific does not contain a null string.

          You get {True, True, True, True, True, True, True, True, True}

          If A8:I8 contained a, b, c, , e, f, g, h,

          then your input vector is {True, True, True, False, True, True, True, True, False}

          Dividing 1 by this vector

          1/{True, True, True, False, True, True, True, True, False}

          gives you

          {1, 1, 1, 0, 1, 1, 1, 1, 0}

          Actually 1/False gives a #DIV/0 error but let’s call it 0 as it works the same way here.

          The way LOOKUP works is to return the lookup value or if that is not found, it returns the nearest smaller value.

          By using 2 as the lookup value, LOOKUP works through the lookup vector for 2 but can’t find it, so it returns the position of the last 1, which in ths example is position 8.

          So you get 8 returned from the result vector.

          If you need more help please start a forum topic so I can send you a workbook with an example of this.

          Regards

          Phil

          Reply
  10. T radha

    November 7, 2019 at 6:19 pm

    Hi please explain vlookup multiple sheets foumula in cleary

    Reply
    • Mynda Treacy

      November 8, 2019 at 9:32 am

      You can see a tutorial for VLOOKUP multiple sheets here.

      Reply
  11. Omayma Nasr

    April 1, 2019 at 7:23 pm

    Thank you for your great offer

    Reply
  12. Bogdan

    January 15, 2019 at 8:51 pm

    Hello , i shoud do an invoice and i must use vlookup formula and i dont know how to do exactly…i must multiply 10*(0.01*2) can you help me please?

    Reply
    • Mynda Treacy

      January 15, 2019 at 9:59 pm

      Hi Bogdan,

      I’m not sure why you need a VLOOKUP to multiply numbers ?

      Can you please start a topic on the forum and supply your workbook so we can better understand what you need.

      Regards

      Phil

      Reply
  13. ramki

    November 22, 2018 at 3:24 pm

    Hi Myrnda,

    I have an excel file with two sheets in two tables.
    Table 1 – having a cost center and cost spread across 12 columns.
    Table 2 – Having the same cost centers and would like to use the vlookup formula to populate the data from Table 1 without having to change the column reference for each of the 12 columns.

    I did try the formula in the article but it does not work and I get an error message.
    Is there a different formula is using this formula for more than one sheet or am I making a mistake?

    Your help would be much appreciated.

    Regards, Ramki

    Reply
    • Mynda Treacy

      November 22, 2018 at 9:45 pm

      Hi Ramki,

      You can use the COLUMN Function to dynamically update the column number argument in VLOOKUP like so:

      =VLOOKUP(lookup_value,table_array, COLUMN(B1),FALSE)

      Assuming the first column you want returned form the table_array is 2.

      If that doesn’t help, or if you still have questions please post your question and sample Excel file on our forum where we can give you a specific answer.

      Mynda

      Reply
  14. Zacharie Loukombo

    September 15, 2018 at 12:02 am

    i have really enjoyed it

    Reply
    • Mynda Treacy

      September 15, 2018 at 7:18 am

      Great to know, Zacharie!

      Reply
  15. Michael Wagener

    August 1, 2018 at 9:34 am

    Thank you – this was helped me get through a bit of a brain-fart moment… lol. Good article!!

    Reply
    • Mynda Treacy

      August 1, 2018 at 9:53 am

      🙂 glad it was helpful.

      Reply
  16. Debbie Russell

    July 6, 2018 at 3:30 pm

    Thank you.

    Reply
  17. Ginta

    May 10, 2018 at 3:43 am

    Nice tutorial

    Reply
    • Philip Treacy

      May 10, 2018 at 4:42 pm

      Thx Ginta

      Reply
  18. yosef

    April 22, 2018 at 11:02 pm

    ohhh i love this….. i have my igcse in two days and this really helped me out

    Reply
    • Mynda Treacy

      April 23, 2018 at 8:44 am

      Glad you found it helpful. All the best with your igcse!

      Reply
  19. Christina

    March 20, 2018 at 7:26 am

    My vlookup will not copy correctly. I have the calculations function set to automatic. I have all the references except the column number as absolute but it stilll produces the exact same answer instead of moving across a formula. Any suggestions why?

    Reply
    • Mynda Treacy

      March 20, 2018 at 9:50 am

      Hi Christina,

      Hard to tell without seeing the formula and workbook. Can you please post your question and Excel file on our forum where we can help you further.

      Mynda

      Reply
  20. Michael Tang

    September 12, 2017 at 12:03 am

    Nicely done.

    Reply
    • Mynda Treacy

      September 12, 2017 at 8:48 am

      Cheers, Michael 🙂

      Reply
  21. Hemapriya

    August 27, 2017 at 12:29 am

    Super cool.

    Reply
  22. Nasarkhan.G

    July 27, 2017 at 11:14 am

    Super awesome..

    Reply
    • Mynda Treacy

      July 27, 2017 at 11:57 am

      Thanks! Glad I could help!

      Reply
  23. Julie

    December 9, 2016 at 12:57 am

    I absolutely love your videos and how you clarify steps and what the formulas mean and what they are extracting. I have watched MANY training videos and yours are the best! THANK YOU!

    Reply
    • Mynda Treacy

      December 9, 2016 at 8:29 am

      Aw, thanks Julie 🙂 Glad you enjoyed them.

      Mynda

      Reply
    • Amani

      July 21, 2017 at 3:30 am

      Same with me I feel it is the best.

      Reply
      • Mynda Treacy

        July 21, 2017 at 9:14 am

        Thanks, Amani 🙂 Great to know I can help.

        Reply
  24. Naveed Yousaf

    November 30, 2016 at 12:43 pm

    Well explained.

    Reply
    • Mynda Treacy

      November 30, 2016 at 12:56 pm

      Thanks, Naveed 🙂

      Reply
  25. anilkumarpadey

    December 13, 2015 at 6:21 pm

    Hi Sir ji Please help me
    if one row lots of code 41 or 37 etc and next row lots of grade A1, A2, B1 i want to know how many code 41 get A1 or how many 37 code A1 what formula used. show below chart what formula use it.
    SUB MRK GRD SUB
    41 88 A2 42
    37 99 A1 42
    41 78 B1 42
    37 94 A1 42
    41 64 B2 42
    41 59 C1 42
    41 79 B1 42
    41 33 D2 42
    41 95 A1 42
    42 65 B2 43
    Advance Thanks

    Reply
    • Catalin Bombea

      December 13, 2015 at 8:55 pm

      Have you tried the formula you already have?
      =SUMPRODUCT(($A$2:$A$12=37)*($C$2:$C$12=”A1“))
      =SUMPRODUCT(($A$2:$A$12=41)*($C$2:$C$12=”A1“))
      Catalin

      Reply
  26. Emmanuel

    November 10, 2015 at 4:12 pm

    Thanks a lot for the ebook Tips & Tricks. Unfortunately, it’s not as what I wanted. I am looking for help to do with lookup database pictures. The result from vlookup data from the table as the result should be picture as wanted. Although, I have some examples from the internet but Sorry I’m not satisfied with them. I hope might be you would help me, please. Thanks a lot.

    Reply
    • Catalin Bombea

      November 10, 2015 at 5:15 pm

      Hi Emmanuel,
      Can you please provide a sample file and detailed descriptions for what you are trying to achieve? You can use our Help Desk System to upload the file, please create a new ticket.
      Cheers,
      Catalin

      Reply
  27. kamalnath

    April 15, 2015 at 9:55 pm

    Hi.. Pl help me identifying the 1st , 2nd and 3rd max values from an array…

    ex:
    Array
    Project Version
    A 13
    A 3
    A 4
    A 2
    A 5
    A 8
    A 18
    A 11
    A 7
    A 23
    A 24
    A 6
    A 12
    A 1
    A 9
    B 5
    B 2
    B 3
    B 1
    B 6
    B 4

    I want the 1st , 2nd and 3rd max values of project ‘A’ in the cells z1, z2, & z3, likewise for project ‘B’ in y1, y2 & y3…

    Pl help…

    thx
    Kamal

    Reply
    • Catalin Bombea

      April 16, 2015 at 3:52 am

      Hi Kamal,
      You can use this formula in Z1, and copy it down to Z3:
      =LARGE(IF($A$1:$A$21=”A”,$B$1:$B$21,0),ROW(A1)) entered with Ctrl+Shift+Enter (it’s an array formula)
      For largest B projects: use this formula in Y1, copied to Y3:
      =LARGE(IF($A$1:$A$21=”B”,$B$1:$B$21,””),ROW(A1)) entered with Ctrl+Shift+Enter (it’s an array formula)
      Cheers,
      Catalin

      Reply
      • kamalnath

        April 16, 2015 at 5:19 pm

        Hi Catalin… It works for me… Thanks very much… Just now I m hearing about array formula.. if you have notes on that, pl share…

        Now I have another request… its continuation of my earlier request…

        Project sub-proj Version
        A qwe 13
        A rty 3
        A uio 4
        A plk 2
        A jhg 5
        A fds 8
        A azx 18
        A cvb 11
        A nml 7
        A kjh 23
        A gfd 24
        A saq 6
        A wer 12
        A tyu 1
        A iop 9
        B mnb 5
        B vcx 2
        B zas 3
        B dfg 1
        B hjk 6
        B lop 4

        I want the sub-proj name of 1st , 2nd and 3rd max values of project ‘A’ in the cells z1, z2, & z3, likewise for project ‘B’ in y1, y2 & y3…

        pl help…

        Thx
        Kamal

        Reply
        • Catalin Bombea

          April 17, 2015 at 2:45 pm

          Hi Kamal,
          Please describe from the beginning the entire problem, because any little detail can change the approach, and the solution may be totally different.
          Try this file from our OneDrive folder. The solution works fine if you do not have duplicates, at least for the highest 3 numbers.
          Cheers,
          Catalin

          Reply
          • kamalnath

            April 20, 2015 at 10:49 pm

            Pl let me know, how to share my xl sheet

          • Mynda Treacy

            April 21, 2015 at 9:20 am

            Hi Kamalnath,

            You can send it to us via the Help Desk.

            Cheers,

            Mynda

  28. kumar

    February 18, 2015 at 8:11 pm

    Madam, I find the tuitorial guide very helpful. Would you please explain to me how we proceed with VLOOKUP using multiples sheet.

    Thanking you

    Kumar

    Reply
    • Mynda Treacy

      February 18, 2015 at 8:19 pm

      Thanks, Kumar.

      You can see the VLOOKUP for multiple sheets tutorial here:

      https://www.myonlinetraininghub.com/excel-vlookup-multiple-sheets

      Kind regards,

      Mynda

      Reply
  29. Peik

    February 10, 2015 at 6:15 pm

    Learn far from here

    Reply
    • Mynda Treacy

      February 10, 2015 at 9:07 pm

      Thanks, Peik 🙂

      Reply
  30. José Andrade

    October 23, 2014 at 7:41 am

    Hello MEL

    Yes their are very lively presentations are excellent and make Excel be simple.

    Reply
  31. Mel

    July 15, 2014 at 10:22 am

    Hi Mynda, I am trying to do some analysis on a s/s. I have a worksheet which has data arranged in columns, such as instance, location, months. I have another s/s with new data for the current month. I am trying to compare data from the current month to the existing s/s and if data (instance and locations) match, then add this new data as a new column to the existing s/s. The instances column should have unique data, but if there are data in the new or existing s/s that do not match, then append them at the bottom of the existing s/s. What formula should I use? Thanks for your help.

    Reply
    • Mynda Treacy

      July 15, 2014 at 12:49 pm

      Hi Mel,

      I hate to say this but I think your approach isn’t ideal. There is no easy way to do what you describe using formulas… or any other Excel tool.

      Perhaps if you can send me your workbook I can better understand what you’re trying to do and give you some advice on how to acheive the same end result with some changes to your process.

      You can send your file and description of what you want and where via our help desk – anything you send is kept confidential.

      In the meantime you might find this tutorial on Tabular Data helpful.

      Kind regards,

      Mynda

      Reply
  32. rahul

    June 17, 2014 at 12:56 pm

    got gd

    Reply
  33. Ranjha

    April 22, 2014 at 5:55 pm

    Very useful material.

    Reply
    • Mynda Treacy

      April 22, 2014 at 7:53 pm

      Glad we could help, Ranjha 🙂

      Reply
  34. murtaza begi

    January 23, 2014 at 12:49 am

    hi can you please sent me a emaill and explean the why we use the VLOOKUP AND ALSO EXPLAN AS WELL THANKS A LOT PLEASE

    Reply
    • Catalin Bombea

      January 23, 2014 at 2:44 am

      Hi,
      VLOOKUP is an Excel Function that is used within tables to help filter through large volumes of data and
      select the appropriate data based on given conditions. The VLOOKUP formula would automatically look through the list of your Objects and pick out
      the corresponding data.

      The function is very well described in this tutorial , please take your time to understand the explanations, you can also download an example workbook, the link for download is at the end of the tutorial.
      Catalin

      Reply
  35. Yogi

    August 11, 2013 at 11:58 pm

    Wonderful, I need some more examples to master this formula. i would also like to learn about sub total.
    Thanks

    Reply
    • Mynda Treacy

      August 12, 2013 at 1:23 pm

      Thanks, Yogi. If you mean the SUBTOTAL Function you can learn it here.

      Otherwise, a tutorial on the Subtotal tool is here.

      Cheers,

      Mynda.

      Reply
      • Harish

        January 13, 2014 at 4:04 pm

        Nice information….

        Reply
        • Mynda Treacy

          January 13, 2014 at 10:15 pm

          Thank you, Harish 🙂

          Reply
  36. P

    July 20, 2013 at 7:03 am

    Loved it. Nice and simple and that “plain english” translation is just superb !!!!

    Reply
    • Mynda Treacy

      July 20, 2013 at 2:44 pm

      Thanks, P. Glad you found it useful 🙂

      Reply
  37. sim

    May 18, 2013 at 4:25 am

    by v look up the answer comes only up in the function arguments but in the cell only coming up v look up (example) but not the answer

    Reply
    • Mynda Treacy

      May 18, 2013 at 6:40 pm

      Sorry, Sim. I don’t understand. Can you please give me an example?

      Cheers,

      Mynda.

      Reply
  38. bns

    May 17, 2013 at 5:14 pm

    Sir,
    In my Laptop Excel sheet’s are viewing as for Rows 1,2,3,… are making visible and for coloums 1,2,3….. are visibling insted of A,B,C… how to chage it?

    Reply
    • Mynda Treacy

      May 17, 2013 at 7:09 pm

      Hi BNS,

      You’ve got R1C1 reference style turned on. To turn it off you need to access the Options (Office button for 2007 or File tab for 2010) > Formulas category > uncheck ‘R1C1 reference style’.

      Kind regards,

      Mynda.

      Reply
  39. Charles Taylor

    May 3, 2013 at 7:40 am

    Can solve this puzzle from a spread sheet from 1996 deals with gas processing Question on VLOOKUP Function This is part of a spread sheet
    Amine Treater
    Amine type (MEA, DEA, MDEA) DEA Typical Amine solution properties are shown below:

    Many cells left out

    Amine Properties Lookup Table
    Amine Wt% Loading SG @ 120F Mole Wt BTU/Gal
    MEA 15 0.33 0.99 61.08 1200
    DGA 50 0.35 1.058 105.14 1300
    DEA 30 0.35 1.02 105.14 1100
    MDEA 50 0.35 1.03 119.16 1000

    Intermediate Calculation Results
    CO2 and H2S to be removed 60.84 lb-moles/hr
    Solution specific gravity 1.04 VLOOKUP(UPPER($C$27),PROP,4,FALSE)
    Amine molecular weight 105.14 =VLOOKUP(UPPER($C$27),PROP,5,FALSE) Do you know how cell DEA($C$27) at the top of the sheet is referenced to the two cells for Specific Gravity 1.04 & Molecular Weight 105.14 is nested or referenced?
    Can’t find the chart UPPER on the spread sheet. PROP is Amine Look Up Table, immediately above the text.
    Could send he spread sheet to you.
    Regards….

    Reply
    • Mynda Treacy

      May 3, 2013 at 1:23 pm

      Hi Charles,

      I think it’s best if you send me the Excel file so I can see what you’re talking about.

      Cheers,

      Mynda.

      Reply
  40. ateny

    April 30, 2013 at 5:16 am

    It is very helpful website I have ever visited. I recommended already to all of my friends and the love it.

    Reply
    • Mynda Treacy

      April 30, 2013 at 7:54 pm

      Thanks, Ateny 🙂

      Reply
  41. David T

    April 25, 2013 at 6:44 am

    Great!

    I got a format from a resigned employee, but I don’t understand “,IF({1,0},…”, I cannot find information why there is {1,0} in the IF.

    =VLOOKUP(B2&E2,IF({1,0},’PT New Sales’!$C$2:$C$200&’PT New Sales’!$F$2:$F$200,’PT New Sales’!$G$2:$G$200),2,FALSE)

    Please help, Thanks

    Reply
    • Mynda Treacy

      April 29, 2013 at 10:33 pm

      Hi David T,

      In the IF({1,0} the 1 = TRUE and the 0 = FALSE.

      The formula is testing to see if the value in B2 is in the range C2:C200, and if the value in E2 is in the range F2:F200, if both match return the value in column G.

      It’s an interesting formula. I have not seen it done this way before. I hope that helps.

      Kind regards,

      Mynda.

      P.S. I have to thank Roberto Mensa for helping me clarify what this formula is doing 🙂

      Reply
  42. Barb Laing

    April 21, 2013 at 9:09 pm

    You bring clarity to Excel. I am not just applying a formula, now I know what each component parts mean.

    Thank you

    Barb

    Reply
    • Mynda Treacy

      April 22, 2013 at 9:24 pm

      That’s my pleasure, Barb 🙂

      Reply
  43. Ramkumar

    April 21, 2013 at 2:24 pm

    S..really it’s a great job.

    Reply
    • Mynda Treacy

      April 21, 2013 at 7:20 pm

      Cheers, Ramkumar 🙂

      Reply
  44. yash

    April 20, 2013 at 4:33 pm

    It is very useful,thanks for that,but i want to ask,if Commissioning rates(As per example) is another excel sheet,so can we use vlookup,I tried but is showing error?

    Reply
    • Carlo Estopia

      April 22, 2013 at 6:52 pm

      Hi Yash,

      Please send your file here HELP DESK.\

      I just want to see why it’s an error and how you did it.

      Cheers,

      CarloE

      Reply
  45. Penelope

    April 12, 2013 at 4:03 pm

    Firstly thanks for an awesome site. I have intermediate excel skills, but you’re explanations have made learning new formulas really easy!

    In relation to VLOOKUP – I’m using it in a training register to confirm who has completed which training on what date. The column with the formula is formatted for dates as dd/mm/yyyy. Some people haven’t completed the training yet, and rather than leaving the cell blank it gives the result 0/01/1900. This is the formula as I’ve put it in the sheet =VLOOKUP($A5,Induction!$A:$C,3,FALSE). Is there any thing I can do to make it leave the cell blank if the reference is blank or perhaps a different formula I could use

    Reply
    • Carlo Estopia

      April 12, 2013 at 5:32 pm

      Hi Penelope,

      All you need to do is put it inside an IF function like this:

      =IF(VLOOKUP(A2,Induction!A1:C11,3,FALSE)=0/1/1900,"",VLOOKUP(A2,Induction!A1:C11,3,FALSE))
      

      More IFs

      Cheers,

      CarloE

      Reply
      • Penelope

        April 15, 2013 at 8:38 am

        Thanks heaps for that CarloE 🙂

        Reply
        • Carlo Estopia

          April 15, 2013 at 12:03 pm

          Hi Penelope,

          My pleasure.

          Cheers,

          CarloE

          Reply
  46. Pero Peric

    April 6, 2013 at 12:56 am

    Dear,
    I need a help with Excel formula, probably it’s easy but I can’t get it!
    I have a table of one month and in 2right columns 2 figures, the form is used for account.
    I made a box with Now() and want to make a formula to take data from table for present day. So first have to confirm same date as today from table and then to take data from 2right columns.
    Thank You Very Much in Advance
    Pero

    Reply
    • Carlo Estopia

      April 7, 2013 at 11:39 pm

      Hi Pero,

      Please send your file via help desk. and we can look at this for you.

      Cheers,

      Carlo

      Reply
  47. arvind

    March 20, 2013 at 12:46 am

    I downloaded the Excel workbook excercises (https://www.myonlinetraininghub.com/excel-2007-%e2%80%93-vlookup-formulas-explained) but they would not open in Excel. It would be appreciated if you can help me to overcome this problem.

    Thank you for your help.

    Arvind

    Reply
    • Carlo Estopia

      March 20, 2013 at 9:44 am

      Hi Arvind,

      I would say either your browser has changed the file extension of the workbook from .xlsx to something else, or you are using Excel version pre-2007?

      You can try again and make sure the file extension is .xlsx of the saved file.

      If you have Excel 2003 or earlier let us know and we’ll make a pre-2007 version available.

      Cheers.

      CarloE

      Reply
  48. HASSAN KARIM

    March 10, 2013 at 7:43 am

    hi mynda,
    can u help me in vlookup formula.i think it is scarry for how use this formula in very easy.i have two difrent sheet in two difrent file .tell me how can i handle this…………plzzzzzzzzzzzzz

    Reply
    • Carlo Estopia

      March 10, 2013 at 2:41 pm

      Hi Hassan,

      Here’s an example. I want to lookup Aquino, Greg’s position in Sheet2

      The Formula:

      =VLOOKUP(A1,'[Workbook2.xlsx]Sheet1'!$A$8:$F$10,2,FALSE)
      

      Data:
      Workbook1:Sheet1

            A                   B
      1 Aquino, Greg	[VLOOKUP FORMULA HERE]Pitcher
      

      Workbook2: Sheet1

      7  Name	         Position	Salary	 Pay Rise 2004 	Pay Rise 2005	Pay Rise 2006
      8  Aquino, Greg	 Pitcher	325,000	 29,250 	 28,340 	 22,955 
      
      
      9  Bruney, Brian Pitcher	322,500	 29,025 	 28,122 	 22,779 
      10 Choate, Randy Pitcher	550,000	 49,500 	 47,960 	 38,848 
      

      Read More: VLOOKUP

      Cheers.

      CarloE

      Reply
  49. Manoj Yadav

    February 19, 2013 at 6:32 pm

    If some time invest the amt 12000 in medicine and after the selling received total amt 18000 , how many % income

    Reply
    • Carlo Estopia

      February 19, 2013 at 11:37 pm

      Hi Manoj,

      It’s a simple formula.
      Data:

        A       B         C   
      12000	18000	0.333333333
      

      Formula:

      =(B1-A1)/B1
      

      I hope it helps.

      Cheers.

      CarloE

      Reply
  50. Proficient

    February 17, 2013 at 6:11 pm

    Hi Mynda!

    We can use Vlookup either side…

    =VLOOKUP(E2,CHOOSE({1,2},$B$2:$B$13,$A$2:$A$13),2,FALSE)

    =VLOOKUP(CRITERIA,CHOOSE({1,2},CRITERIA RANGE,LOOKUP RANGE),COLUMN NUMBER,FALSE)

    Reply
    • Carlo Estopia

      February 18, 2013 at 11:10 am

      Hi Proficient,

      Thanks for sharing.

      You may also read our similar post here: VLOOKUP and CHOOSE

      Cheers.

      CarloE

      Reply
  51. Jasim

    February 15, 2013 at 12:39 am

    Many Thanks..

    Reply
    • Mynda Treacy

      February 15, 2013 at 9:02 am

      Cheers, Jasim. You’re welcome 🙂

      Reply
  52. mohammed adil

    February 9, 2013 at 2:59 pm

    Hi
    I have two tables one with colum name as “login id” and another with “computer name” and same colum in other sheet. i just want to compair login id and copy respective computer name to it. i tryed following funtion
    =VLOOKUP(A2,sheet2!A:b,2,0) result is #N/A

    Reply
    • Carlo Estopia

      February 9, 2013 at 6:48 pm

      Hi Mohammed Adil,

      Your VLookup should look like this:

      =VLOOKUP(A2,Sheet2!A2:B4,2,0)
      

      Your Table Array part don’t have the row arguments. It must have the numbers in other words i.e. Shee2!A2:B4.

      Read more on VLOOKUP BASICS

      Sincerely,

      CarloE

      Reply
  53. Lynn

    February 8, 2013 at 2:02 am

    I have used this lookup formula for years with complete confidence.
    =if(vlookup(cell,range,1)=cell,vlookup(cell,range,Column # to be returned),” “). This returns an exact match if found and a blank cell if not.

    But I have run into a problem, my formula is not returning anything on some newly added items in the lookup range. The item are still in sorted order and still in the lookup range. The format of the information is a match. Have you come across this?

    Reply
    • Carlo Estopia

      February 8, 2013 at 3:15 pm

      Hi Lynn,

      Please try to send your file through HELP DESK so we can have a good look at your problem.

      Anyways, my diagnosis is that you did not have absolute references to your table_array part
      of your VLOOKUP.

      For example

       =IF(VLOOKUP(G5,$C$5:$D$9,1)=G5,VLOOKUP(G5,$C$5:$D$9,1),"") 

      You can do this by highlighting the ranges -only i.e. C5:D9- and press F4; or
      You can simply put Dollar($) sign manually.

      Cheers.

      CarloE

      Reply
  54. 6tel

    January 13, 2013 at 1:03 pm

    Hi Mynda. The best for you in 2013… As usual, finding the best answers here… Excellent job, really.

    Mynda, I’m having a trouble. Your explanation was great on the Vlookup formula syntaxis, but I was just wondering if the “col_index_num” requirement would look into rows instead of columns…. How would achieve that? I guess this function isn’t going to work for me, since I need to return a value that’s five rows under the respective “lookup_value” reference, and not to the side…

    Is there an equivalent to this formula, but reading from the top of a table to its bottom?

    =VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

    Reply
    • 6tel

      January 13, 2013 at 2:12 pm

      Hi Mynda. I guess I found it and did it, using the HLOOKUP function that you also explained here in the blog (sorry):

      =HLOOKUP(AOR35,AK4:BG9,6,FALSE)

      Thank you very much for your lessons.

      Reply
      • Mynda Treacy

        January 15, 2013 at 1:43 pm

        Hi 6tel,

        Happy New Year to you too 🙂 Glad you found the HLOOKUP solution.

        Kind regards,

        Mynda.

        Reply
  55. santhosh

    January 5, 2013 at 1:48 am

    is very yousfull my life in my care

    Reply
  56. SREEDHARA

    December 25, 2012 at 12:32 pm

    NICE WAY OF PRESENTING THAT’S TO EXLPNATION IN ENGLISH I.E –
    And to translate it into English it would read:

    VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly)
    VERY SUPERB
    TNK U

    Reply
    • Mynda Treacy

      December 26, 2012 at 6:45 pm

      Thanks, Sreedhara 🙂

      Reply
  57. Reeta Khetrapal

    December 23, 2012 at 1:49 am

    How to search multiple entries by using Vlookup

    Reply
    • Mynda Treacy

      December 23, 2012 at 8:34 pm

      Hi Reeta,

      Here is a tutorial on VLOOKUP multiple values.

      If that’s not what you want there is a list of different VLOOKUP tutorials here, including multiple criteria, multiple columns and returning multiple values.

      Kind regards,

      Mynda.

      Reply
  58. Viswanathan

    December 22, 2012 at 6:59 pm

    Excellent way of explaining. Easy to understand

    Reply
    • Mynda Treacy

      December 23, 2012 at 8:28 pm

      Thanks, Viswanathan 🙂

      Reply
  59. Devikarani

    December 20, 2012 at 5:26 pm

    Great help for searchers like me. Thanks a ton..

    Reply
    • Mynda Treacy

      December 21, 2012 at 10:46 am

      You’re welcome, Devikarani 🙂

      Reply
  60. Martin Williamson

    December 12, 2012 at 10:38 pm

    I have a challenging excel vlookup problem I can’t solve and I can relate it to your example.

    In my problem there is an additional column in the table of rates called “effective date”.

    Insert a new column between Column G:H, and add title “Effective Date” Then give all those in your rates table an effective date of 01 Jan 08.

    However, Dave gets a raise on 01 Mar 08 to 6% commission. (Well done Dave!)

    Insert a new entry below Dave’s 01 Jan 08 entry and add:
    01 Mar 08 Dave 6%

    However, with your current formula, it does not find Dave’s new rate for sales after 01 Mar 08.

    I have tried index match formula’s but also to no avail.

    I really, really, hope you’re able to help as this has been bugging me for months.
    Many thanks for your time. Regards, Martin

    Reply
    • Mynda Treacy

      December 13, 2012 at 9:35 pm

      Hi Martin,

      If you know you are looking for the date on 01 Mar 08 you could use SUMIFS:

      =SUMIFS($I$5:$I$6,$G$5:$G$6,"Dave",$H$5:$H$6,"01/03/2008")

      or if you’re using Excel 2003 use SUMPRODUCT:

      =SUMPRODUCT((I5:I6)*(G5:G6="Dave")*(H5:H6=DATEVALUE("01/03/2008")))

      However, if you’re just looking for the last record for Dave (your data would need to be sorted in ascending order) then you can use:

      =VLOOKUP("Dave",$G$5:$I$6,3,TRUE)

      or

      =LOOKUP("Dave",$G$5:$G$6,$I$5:$I$6)

      Where column G contains your names, H your dates and I contains your commission rates.

      Kind regards,

      Mynda.

      Reply
      • Martin Williamson

        December 13, 2012 at 10:11 pm

        Thanks for the prompt reponse and the numerous solutions.

        I’ll try those out and see if I can get them to fit my situation.
        Again, many thanks, your prompt response has been much appreciated. 🙂

        Reply
  61. gshephard

    December 1, 2012 at 12:34 am

    Finally found a clear explanation in plain english on how to use this function. Wish i had found this site 1 hour ago! Thanks!

    Reply
    • Mynda Treacy

      December 1, 2012 at 7:47 am

      🙂 Thanks, Garth.

      Reply
  62. mutalib

    November 21, 2012 at 10:20 pm

    i want to know about pivot table in Excel 2007

    Reply
    • Philip Treacy

      November 22, 2012 at 4:35 pm

      Here’s a link to some tutorials on Pivot Tables https://www.myonlinetraininghub.com/pivot-table

      Phil

      Reply
  63. Prakash

    November 20, 2012 at 4:56 pm

    Here we’re doing VLOOKUP in same worksheet,
    can you give me VLOOKUP formula using different worksheet.

    Reply
    • Mynda Treacy

      November 20, 2012 at 6:59 pm

      Hi Prakash,

      When you build your VLOOKUP formula you can use your mouse to select the cells on another worksheet. Excel will automatically put in the cell references for you.

      Kind regards,

      Mynda.

      Reply
  64. Trudi

    November 9, 2012 at 3:53 pm

    Great tutorials – thankyou for sharing your knowledge. Greatly helped me in my work.

    Reply
    • Mynda Treacy

      November 9, 2012 at 5:57 pm

      You’re welcome, Trudi 🙂

      Reply
  65. Steven Pofcher (@spofcher)

    November 4, 2012 at 12:49 am

    Nice. I especially like the extending of the VLOOKUP to do calculating.

    Reply
    • Mynda Treacy

      November 4, 2012 at 12:07 pm

      Cheers, Steven 🙂

      Reply
  66. Ana da Silva

    November 1, 2012 at 3:56 pm

    Finally. I have found someone that can translate “computer lingo” into English.

    I have been lost in the land of technology, and then……. I have found my little slice of heaven. This website.

    From one bean counter to another. THANK YOU.

    Reply
    • Mynda Treacy

      November 1, 2012 at 8:27 pm

      🙂 Wow, thanks Ana. I’m glad you can relate to my English translations!

      Reply
  67. Lynn Ashworth

    November 1, 2012 at 6:55 am

    This is excellent. I have been trying to teach myself vlookup today using the Help option and it didn’t help! What a difference it makes to actually see the data and have it so clearly explained. You have de-mystified vlookup for me and I’m now looking forward to getting to work tomorrow to try it out on my spreadsheet! Thank you very much.

    Reply
    • Mynda Treacy

      November 1, 2012 at 7:11 am

      🙂 Glad I could help, Lynn.

      Reply
  68. AV

    September 27, 2012 at 5:50 am

    Awesome breakdown of the VLOOKUP formula. This saved my day at work. I came here because it seemed intimidating but after seeing the tutorial, I now scoff at its fear-factor, lol. You also have a hot voice. Extra brownie points for you! : )

    Reply
    • Mynda Treacy

      September 27, 2012 at 3:19 pm

      Hi AV,

      I’m glad I took the ‘scary’ out of Excel for you 🙂

      Kind regards,

      Mynda.

      Reply
  69. Sadaqat Hussain

    September 26, 2012 at 4:56 pm

    it is powerful and wonderful but I d’nt now how to utilize it

    Reply
  70. Chris K

    September 21, 2012 at 7:54 am

    Mynda,
    Is there any way to have a vlookup formula present where if the fields are left blank you can have a return of 0 instead of N/A? I tried including a blank line item in my chart with a 0 value but it still comes back as N/A and is killing my totals.
    I can show you my work so far but it is getting messy!

    Reply
    • Mynda Treacy

      September 21, 2012 at 8:22 am

      Hi Chris,

      You can use IFERROR with your VLOOKUP to return any value you’d like if the result is not found in the table.

      Click the link above to see an example.

      Kind regards,

      Mynda.

      Reply
      • Manjinder Mavi

        December 11, 2012 at 9:26 am

        Hi Mynda,

        Your “plain english” style of explaining things motivated me to learn more.

        Related to Chris K’s query above, VLOOKUP puts a value 0 if the corresponding value cell (column index cell) is empty. It messes up my other calculations. Is there any way to get VLOOKUP with help of other function to return a particular value (say “Empty”) if the cell is empty.

        I understand we can use IFERROR with VLOOKUP the lookup value can’t be found. But I am interested when the lookup value is found but the corresponding column index cell for that lookup value is empty.

        Thanks.

        Best regards,
        Manny

        Reply
        • Mynda Treacy

          December 11, 2012 at 4:18 pm

          Hi Manny,

          You can wrap the VLOOKUP formula in an IF function like this:

          =IF(VLOOKUP(A1,B1:C10,2,FALSE)=0,”Empty”,VLOOKUP(A1,B1:C10,2,FALSE))

          Kind regards,

          Mynda.

          Reply
  71. Imran

    September 12, 2012 at 6:53 am

    Dear Mynda,

    I like your videos , especially your style of narrating complex problems in a simple and effective manner.

    Keep up the good work and Allah bless you.

    Regards
    Imran

    Reply
    • Mynda Treacy

      September 12, 2012 at 8:41 am

      🙂 Thank you for your kind words, Imran.

      Reply
  72. Cheryl Blalock

    August 30, 2012 at 4:10 am

    I’m currently using vlookup in my work. Now I have an problem to solve. I have two reports that I need to work with. One FY12 customer sales with part numbers. The other FY13July customer sales with part numbers. I want to put the FY13July sales number in a column on the FY12 File and add to each month end so we have a running total. The problem is the look up value is not unique. Many customer buy the same part #. Is there a way to use 2 cells as the look up value? (acct # & part #) Thanks for your help.

    Reply
    • Mynda Treacy

      August 30, 2012 at 6:58 am

      Hi Cheryl,

      You need to make a column of values by joining the acc# and Part# together using CONCATENATE. Perhaps do this in your FY13 data. Then use this technique to look up the FY12 data: VLOOKUP looking up multiple values.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  73. kashee

    August 24, 2012 at 6:40 pm

    Great job !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Reply
    • Mynda Treacy

      August 24, 2012 at 8:05 pm

      Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 🙂

      Reply
  74. Vaidehi Raval

    August 14, 2012 at 1:50 pm

    Excellent! cheers :).

    Reply
    • Mynda Treacy

      August 14, 2012 at 10:02 pm

      Thank you!

      Reply
  75. Andi

    July 14, 2012 at 3:11 am

    Thanks so much! Starting a new job and had to refresh my vlookup skills! Great website. I’ll be back.

    Reply
    • Mynda Treacy

      July 15, 2012 at 11:10 am

      Cheers, Andi 🙂 All the best in your new job.

      Reply
  76. Janette Goodson

    June 1, 2012 at 11:25 pm

    I find these exercises very informative and fun to do.

    Reply
    • Mynda Treacy

      June 2, 2012 at 10:46 am

      Thanks, Janette. Glad you like them 🙂

      Reply
  77. Ramamoorthi

    May 25, 2012 at 9:32 pm

    The article was simple and easy to understand. It cleared the doubts I had about VLOOKUp function. I want more such articles to improve my working with Excel. Thanks a lot

    Reply
    • Mynda Treacy

      May 26, 2012 at 10:12 pm

      Thanks Ramamoorthi! You can find an index of Excel tutorials here.

      Kind regards,

      Mynda.

      Reply
  78. Janell

    April 18, 2012 at 1:04 am

    Thank you

    Reply
  79. guru

    March 30, 2012 at 11:48 pm

    cool…

    Reply
  80. anish kumar

    February 23, 2012 at 10:43 am

    Brilliant web site, Carry on the wonderful work

    Reply
    • Mynda Treacy

      February 23, 2012 at 10:08 pm

      Thanks, Anish 🙂

      Reply
  81. shipra

    February 22, 2012 at 10:08 pm

    It is definitely very helpful !!

    Reply
  82. KARTHIK

    January 30, 2012 at 10:10 pm

    GOOD

    Reply
  83. Philip

    January 17, 2012 at 6:59 am

    Thanks heaps

    Reply
  84. SrSr

    September 14, 2011 at 4:52 pm

    Good One…!!!

    Reply
    • Mynda Treacy

      September 14, 2011 at 8:34 pm

      Thanks SrSr! Glad you liked it.

      Reply
  85. ola

    August 30, 2011 at 3:47 am

    Awesome

    Reply
    • Mynda Treacy

      August 31, 2011 at 9:23 am

      Thanks Ola.

      Reply
  86. Jamie Iomo

    December 8, 2010 at 4:41 am

    how are you?

    Looking forward to your next post

    Reply
  87. Joe

    December 3, 2010 at 3:44 pm

    Wow, thanks for the great info I will definately link you on my blog.

    Reply
  88. JasonybRider

    December 2, 2010 at 3:51 am

    Awesome blog thank you! You should checkout my site

    Reply
  89. AnarhieS

    November 25, 2010 at 5:27 pm

    VLOOKUPS are very powerful and under used in my opinion. I guess alot of people don’t knwo how to use them

    Reply
  90. Liz

    November 15, 2010 at 3:02 pm

    damn apostrophes have caught me out too with text

    Reply
  91. Shaun

    September 30, 2010 at 6:57 am

    Brilliant web site, Carry on the wonderful work!

    Reply
  92. Mynda

    September 29, 2010 at 8:31 pm

    @RHC – nope, passionate as ever!

    @Liz – great. Glad we can help.

    Reply
  93. Liz

    September 28, 2010 at 4:43 pm

    yeah my dad will like this

    Reply
  94. RHC

    September 27, 2010 at 5:20 pm

    Awesome post tim, it’s been a while since I’ve been on here. I see that nobody has lost their passion. Good to be back.

    Reply

Trackbacks

  1. Excel HLOOKUP Formulas Explained says:
    November 26, 2010 at 1:38 pm

    […] is just like the VLOOKUP formula only the table you are looking up is laid out in a horizontal list, rather than […]

    Reply
  2. Excel IFERROR Puts an End to Messy Workarounds says:
    September 25, 2010 at 9:44 pm

    […] we enter our formula in Excel, and apply it to the example we used for our VLOOKUP Exact Match example it would look like […]

    Reply
  3. Excel VLOOKUP v Sorted List Explained says:
    September 22, 2010 at 12:16 pm

    […] my previous Excel 2007 VLOOKUP tutorial I told you that there are two ways you can use a VLOOKUP but most people know one way or the other, […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.