My Online Training Hub

Microsoft Office Online Training

Get our newsletter and join over 80,000
people who learn Excel with us every week
You are here: Home / Excel Formulas / Excel VLOOKUP Multiple Values

Excel VLOOKUP Multiple Values

by 129 Comments

Excel VLOOKUP Multiple Values

For some of us we use the VLOOKUP function all the time and for the most part is does exactly what we want, but what if you want to lookup multiple columns?

Taking the example below; in cell B3 I have a data validation list that allows me to choose the player I want to look up.

Then in cell C3 I have the SUM of the Pay Rises for 2004 through to 2006 for that player. i.e. the values in columns D, E and F.

Excel VLOOKUP Multiple Columns

With a bit of help from an Array formula we can use our trusty VLOOKUP to do just this.

VLOOKUP Multiple Values Formula

In cell C3 I used the following formula to achieve this multiple VLOOKUP result:

{=SUM(VLOOKUP(B3,Table1[[Name]:[Pay Rise 2006]],{4,5,6},FALSE))}

Note: This is an array formula and so the curly brackets at the beginning and end are entered by Excel automatically when you enter the formula by pressing CTRL+SHIFT+ENTER but you need to type the curly brackets in the middle around the {4,5,6} as Excel doesn't automatically enter these. More on Excel array formulas.

The Syntax for the above formula is:

=SUM(VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]))

Breaking each component of the formula down:

  1. lookup_value: B3 – This is the name we choose from the data validation list.
  2. table_array : Table1[[Name]:[Pay Rise 2006]] – Our table or data range is an Excel Table hence the data range has the name ‘Table1’. You could easily replace this reference with a regular data range e.g. $A$8:$F$34 or a named range.
  3. col_index_num: {4,5,6} – Usually the column index number will be just one column in your table, but because we want to reference 3 columns, (Pay Rise 2004, 2005 and 2006), we’ve used an array which houses the 3 columns we want to reference (the array is defined by the curly brackets).
  4. [range_lookup]: FALSE – this simply instructs Excel to find an exact match for the
  5. SUM – Sum the results from columns 4, 5 and 6.

If you liked this please click the Facebook Like button below and then sign up for our Free Excel Newsletter for more tips like this.

Free 100 Excel Tips & Tricks eBook – Click here to Download

Leave a Reply

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

Current ye@r *

Comments

    • Hi David,
      There is no link to the file, you can apply the formula from this article on your table, just adjust the ranges, and don’t forget to confirm with Ctrl+Shift+Enter after editing the formula.
      If you still have problems, upload your test file to our Help Desk and i will help you.
      Cheers,
      Catalin

      Reply

    • Hi, Mynda.. Thank you for the help. But why do I need to enter shift-ctrl-enter for the formula to work?

      Also, can you provide more tutorials on both pivot tables and vlookup? I’m trying to undderstand this when I apply for finance jobs.

      Appreciate your help and looking forward to your reply.

      Reply

  4. Fill in the assigned shift/job based on employee and date Time

    EMPLOYEE date & time Dept/Job

    DR 6/16/14 3:15 PM

    EMPLOYEE Shift Start Date & Time Shift end date & time Dept/Job
    AN 6/15/14 5:00 PM 6/15/14 11:00 PM PMOD – MRH-WEND
    BR 6/15/14 8:00 AM 6/15/14 4:59 PM PEMB AMOD-WEND

    Best Regards,

    Michael F. Mini
    Radiology Associates of Hollywood
    9050 Pines Blvd, Suite 200
    Pembroke Pines, FL. 33024
    (954)437-4800, ext 2104

    Reply

    • Thank you Catalin.
      I am having trouble with the formula. When i modify the the time to match or when I copy the formula. I don’t see where the issue is.

      Reply

      • Hi Mike,
        Please use our Help Desk system to upload your file with changes you made. I hope you noticed that it’s an array formula that should be entered with Ctrl+Shift+Enter, not just enter after editing the formula.
        Regards,
        Catalin

        Reply

          • Hi Mike,
            If you double click on cell H6, in the file you uploaded, you’ll notice coloured rectangles around cells F3 and G3, this means that the formula from H6 is refering to cells F3 and G3, not F6 and G6, which is obviously not right. You can simply drag down those coloured rectangles to F6 and G6 (G3 has 2 of these)
            I also made this change to the formula:

            (Table2[Shift Start Date & Time] >=$G$3)
to:
(Table2[Shift Start Date & Time] < =$G$3)

            Catalin

  7. hi,

    How can we vlookup the total sheet ( columns in single vlookup)
    as in.

    1st colunm contains the common numbers in both the sheet but we have to vlookup 20columns from one sheet to another.

    Kindly help.

    Regards
    Arun

    Reply

    • Hi Arun,
      You have to use VLOOKUP for all columns:
      Assuming you have the identifier in column A, in column B put this formula: =VLOOKUP(A2,Sheet1!A2:L100,2). This will get you the data from column B, sheet1. This one, placed in column C: =VLOOKUP(A2,Sheet1!A2:L100,3) will get data from column C, Sheet1.
      To fill this formula more easily, you can use this version, placed in B2, which will auto fill the column number:
      =VLOOKUP($A2,Sheet1!$A$1:$L$100,Column()) Copy this to the right and down as needed.
      Catalin

      Reply

  8. I have a spreadsheet that has employee numbers in column A and corresponding pay for that week in column D,E,F. I want to be able to find all Direct Labor (DL) employees by employee number in column A and then find all values in column D,E,F that correspond the DL employee and add the dollar value as a single value. As an example.. i have employee number 15, 27, 48 and 52 as DL employees. I want excel to lookup those numbers in column A and then go to column D,E,F and add all the dollar values from those specific employee numbers.

    Reply

    • Hi Al,
      Can you please upload a sample workbook with detailed information of what are you trying to achieve? It’s hard to work on descriptions only, we can find a solution for you a lot faster if we have a file to work with, i’m sure you can understand that 🙂
      You can use the Help Desk: https://www.myonlinetraininghub.com/helpdesk/
      Thank you,
      Catalin

      Reply

      • Dear Mynda
        In your first example, you explained
        col_index_num: {4,5,6} – Usually the column index number will be just one column in your table, but because we want to reference 3 columns, (Pay Rise 2004, 2005 and 2006), we’ve used an array which houses the 3 columns we want to reference (the array is defined by the curly brackets).
        Now my question is that if I have to take sum of large number of columns , say from column no 4 to column no 20, do i need to write {4,5,6,…,20} or is there some better way?

        rgds
        Gyan

        Reply

        • Hi Gyan,

          Instead of {4,5,6…} use COLUMN(D:T) then in the formula bar select the ‘COLUMN(D:T)’ in the formula and press F9. This will give you a list of numbers 4 to 20 like this:

          {4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}

          Or if you don’t want the numbers listed you can enter the formula as an array formula with CTRL+SHIFT+ENTER.

          Kind regards,

          Mynda.

          Reply

  10. Thank you for the tips. But I need some assistance. Here goes….I need to pull data from one tab in excel into another but I only want it to pull certain info.

    I have a drop box with the locations listed and another one with dates. What I need to able to is pull certain data off my data tab when I select a location and date. Any assistance would be greatly appreciated.

    Reply

      • I am stuck. I was able to get the vlookup to pull the data in one of the cells but I can not figure out how to make it to where if I change the date that it pulls from another cell within the data page. Below is part of my formula. =VLOOKUP($B$1,DATA!$E$2:$AJ$2624,7,FALSE)

        What I need to add is F1 (which is where the date is located) and the data tabet goes from d2 to aj2426. I have a HUGE data page.

        Reply

        • Hi Karron,

          It’s best if you send me your workbook via the help desk as I don’t know what you mean by ‘What I need to add is F1 (which is where the date is located) and the data tabet’.

          Cheers,

          Mynda.

          Reply

  11. How do i change the columns to rows, if i would like to vlookup then sum all the results found in rows and not columns?

    Reply

  12. Using VLookup is great however, there will be instances where faculty teaches two or more classes with different FTE (Full Time Equivalent) percentages. Can VLookup return multiple values? If so, how can I incorporate it into the formula used?

    My spreadsheet contains 4 tabs, Biological Sciences, CSS, Eng&Math, PhySci; each tab contains faculty name, Position, Qtr, year, teaching assignment, salary, and FTE

    =IFERROR(VLOOKUP(A2,’Biological Sciences’!$A$1:$W$36,2 ,FALSE),IFERROR(VLOOKUP(VLookup!A2,’Biological Sciences’!$A$1:$W$53,2,FALSE),IFERROR(VLOOKUP(VLookup!A2,CSS!$A$1:$W$53,2,FALSE),IFERROR(VLOOKUP(VLookup!A2,’Eng & Math’!$A$1:$W$53,2,FALSE),IFERROR(VLOOKUP(VLookup!A2,PhySci!$A$1:$W$53,2,FALSE),””)))))

    Thank you for your help!

    Rose Cabrales

    Reply

    • Hi Rose,

      VLOOKUP can SUM multiple values as it’s doing in the example in my post above but from the look of your formula it may not work. If you can please send me the workbook via the Help Desk I can give you a tailored solution.

      Cheers,

      Mynda.

      Reply

  13. Mynda, Thanks very much for this incredibly informative tip, I’ve learned so much from reading your initial posting and subsequent replies to comments! I have a challenge I hope you can help with; I’m trying to find unique text in a single column cell, then count occurrences of different unique text in the row of cells adjacent to that column cell. It seems a combination of COUNTIF(S) and VLOOKUP is called for, but I can’t seem to frame it quite properly. A pivot table works, but I’m summing the occurrences on a separate worksheet and can’t extract the sum from that pivot table. Can you help?

    Thanks,
    Erich

    Reply

  14. Thanks Mynda, while this certainly helps in many situations, I have unable to find a solution (without using VBA) for one of my req, which is, to do exactly what vlookup do, but using multiple columns. While sumproduct and countif most certainly covers all required scenarios, but returning a string value in column C nased on whether colA (string match) and ColB (string match) have a certain value or not could not be achieved. your help with the same (without VBA) will be highly appreciated.
    Regards,
    Sattam

    Reply

  15. thanks for sharing this great stuff. Ordinarly, we would ve use sum (vloop1,vlookup2,vlookup3). But the stuff given by u, is great use of array and smart also.

    Reply

  18. Whenever I think that I found out everything there is to know about the function vlookup I am made to realize that there are more to know

    Reply

  19. Hi,

    I have a lookup that has me stumped. I have read through all your other posts and I cant seem to get a vlookup to do what I need for me to manage my household budget.

    Now I have done an extract of my bank statement to excel and added a new column where I have catagorised each item, e.g. Mortgage, Petrol, Insurance, Child Care etc.

    Now from another sheet what I want to be able to do is say take all the items for the month catagorised as say Petrol and put the sum of these values into my budget tab

    Date: Description: $: Category:
    Col A: Col B: Col C: Col D:
    1/3/13, Description x, 20, Petrol
    5/3/13, Description x, 20, Mortgage
    9/3/13, Description x, 50, Petrol
    19/3/13, Description x, 50, Petrol
    23/3/13, Description x, 25, Child Care
    30/3/13, Description x, 25, Insurance

    So what I want returned to my cell is 120 for petrol for the month of March.

    Any ideas?

    Reply

    • Hi Damien,

      Try this formula and assume the data below:

      =SUMIFS(C2:C7,D2:D7,"=Petrol",A2:A7,">=3/1/2013",A2:A7,"< =3/31/2013")

      Data:

        A                  B          C         D
3/1/2013	Description x	20	Petrol
3/2/2013	Description x	20	Mortgage
3/3/2013	Description x	50	petrol
3/4/2013	Description x	50	Petrol
3/5/2013	Description x	25	Child Care
3/6/2013	Description x	25	Insurance

      Take note of the date format. It will help. Just be consistent with it.

      Cheers,

      Carlo

      PS: SUMIFS

      Reply

  21. Hi,

    I am having a little bit of difficulty with using the VLOOKUP, I want to be able to add up my columns, then divide them for an average between all 7. So entering data into a secondary sheet though when that particular item is selected from a drop down box, i want the values represented in the columns to add up and then my divided in order to give me the average number between them.

    It has been wrecking my brain, and I am really hoping for a simple explanation.

    =(VLOOKUP([@[Mob ‘#]],’Daily Feed Intake per Hd’!A3:J19,{sum(3,4,5,6,7,8,9,10},FALSE))/7

    I really hope you can shed light on this.

    Thanks, Jess

    Reply

  22. Hi Mynda,

    i have 1 query.. if Brian is twice or thrice in column “A” and there is value only in “C”.. so can i get total qty for this?

    i tried but i couldn’t get it… 🙁

    Reply

      • Thanks for file . 🙂

        see below Eg. for my question.

        mango-5
        banana-10
        apple-15
        mango-10
        apple-5

        so i want lookup result like mango=15
        apple=20

        is it possible ..?

        regards,
        Nilofar

        Reply

        • Hi Nilofar,

              A      B
1 mango	   5		=SUMIF($A$1:$A$5,A1,$B$1:$B$5) or =SUMIF($A$1:$A$5,"mango",$B$1:$B$5) result:15
2 banana  10		
3 apple	  15		=SUMIF($A$1:$A$5,A3,$B$1:$B$5) or =SUMIF($A$1:$A$5,"apple",$B$1:$B$5) result:20
4 mango	  10		
5 apple	   5

          Read more: SUMIF

          Cheers.

          Carlo

          Reply

  23. Dear Mynda,

    I am working on 2 sheets within the same excel sheet. In master sheet there are 3 columns A, B, C. In the Child sheet, A and B are calculated based on certain parameters. Now, I need to map the value of A & B from the child sheet and get the Value of C from the Master sheet. Please guide me to do the same.

    For example A=5,00,000 and B=50% in child sheet. In master sheet, I need to match Value of A and B together and get the value of C, which is in the master sheet to my child sheet.

    AND

    I need to round up 2 digits number in the nearest 10 multiples and 3 digits number to the nearest 3 digits multiple. How is it possible within the same formulae.

    For example if the value is 76, it should come 80 and if the value is 176, it should come 200. It needs to be done within the same formulae.

    Thank you in advance and please suggest me at the earliest. It is urgent 🙂

    Reply

    • Hi Ansh,

      Here’s the overall formula. Don’t be overwhelmed. Just follow the lead. It’s all about copying the formulas below to the
      Child Sheet.

      THE OVERALL FORMULA TO C5 of the Child Sheet.

      =IF(LEN(myVlookup(A5&B5,Ansh!$A$2:$C$16,3))< =2,IF(INT(RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),1))>5,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)-RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),1)+10,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)),IF(INT(RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),(LEN(myVlookup(A5&B5,Ansh!$A$2:$C$16,3))-1)))>50,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)-RIGHT(myVlookup(A5&B5,Ansh!$A$2:$C$16,3),(LEN(myVlookup(A5&B5,Ansh!$A$2:$C$16,3))-1))+100,myVlookup(A5&B5,Ansh!$A$2:$C$16,3)))

      Assumptions:
      Ansh is the MasterSheet
      Child is the ChildSheet

      Data:
      Ansh
      —A—– –B–
      2 5000000…50%

      Child
      —A—– –B—– —C—
      1 1000000…50%…
      2 2000000…50%…
      3 3000000…50%…
      4 4000000…50%…
      5 5000000…50%… The Complete Formula as tested

      Formula Broken Down: with their isolated results
      1) The VBA function:Copy to D5 of Child 

       myVlookup(A5&B5,Ansh!$A$2:$C$16,3)


      You MUST do this:
      1 ALT+F11 (Brings the VBE WINDOW)
      2 Go to Insert, Add Module (note: not Class Module)
      3 Paste this Code in the Module

      Function MyVLookup(LookUpValue As String, TBLArray As Range, Col_Return As Integer) As Variant
    Dim r As Long
    MyVLookup = ""
    Dim str As String
    If LookUpValue = "" Then Exit Function
    For r = 1 To TBLArray.Rows.Count
        str = TBLArray.Cells(r, 1).Value & TBLArray.Cells(r, 2).Value
        If TBLArray.Cells(r, 1).Value & TBLArray.Cells(r, 2).Value Like LookUpValue & "*" Then
            MyVLookup = TBLArray(r, Col_Return).Value
            Exit Function
        Else
        End If
    Next
    MyVLookup = "NoMatch"
End Function

      2 THE EXCEL FORMULA FOR ROUNDING UP: COPY TO E5 of Child

      =IF(LEN(D5)< =2,IF(INT(RIGHT(D5,1))>5,D5-RIGHT(D5,1)+10,D5),IF(INT(RIGHT(D5,(LEN(D5)-1)))>50,D5-RIGHT(D5,(LEN(D5)-1))+100,D5))

      Child
      —A—– –B—– —C————————— —-D—- —–E—————————–
      1 1000000…50%…
      2 2000000…50%…
      3 3000000…50%…
      4 4000000…50%…
      5 5000000…50%… The Complete Formula as tested..MyVLookup..THE EXCEL FORMULA FOR ROUNDING UP.

      Cheers.

      CarloE

      Reply

  24. Hi thanks for your valuable tips its really worth considering these help.

    Just wanted to bring it to your notice that Vlookup multiple value & vlookup Multiple coloum contains same working. Could you pls check

    Thanks

    Reply

    • Hi Kamala,

      Yes, it’s the same tutorial. Some people refer to it as looking up multiple values and some refer to it as looking up multiple columns. So we have listed the same tutorial under both descriptions.

      Sorry for any confusion.

      Kind regards,

      Mynda.

      Reply

  25. Hi Mynda,
    I have a question about how vlookup many cells in one column without “=concatenate” function.
    For example:
    A column; B column
    DDD Yes
    AAA Yes
    CCC No
    EEE Yes
    RRR Yes

    i want create formula in C2 cell which concatenate texts in A column if in B column indicated “Yes”. Result will be shown how (DDD, AAA, EEE, RRR).

    Thanks in advanced.

    Reply

      • Dear Mynda,
        the cells count is equal 10000 – 15000. With Vlookup function cannot concatenate many specific cells.

        Thanks in advance.

        Reply

  26. Greta site; I’ve looked at numerous tips & tricks — so many things I didn’t know!

    However, I can’t seem to locate anything (at least that I can understand) that can help me with a particular excel problem I’m having. I’ve been on the internet help boards but still have no replies. Do you have anyone that might be willing to offer some guidance on an excel problem? I work for a career college and I’m having trouble with a solution on course scheduling.

    thanks,
    Chuck

    Reply

  27. Hi Mynda,
    I am working on a project and would really need your help. Basically, I have a table of list of company names and each company have say, 5 owners. What I was trying to do is to put the company name in a cell through data validation and once you choose/select a certain company in your drop down menu, you should have the list of owners of that company to be automatically shown on the cells below the company names. Is there a way to do this?

    Reply

  28. Hi there and thanks for the tips. I’ve been able to follow the vlookup and the sum vlookup and create my own examples, but what I really need is some sort of combination of a lookup, an array and a horizontal display result (I think).

    Okay in my example I have a table, called pricelist and its in cells N15 to Q19:

    pen 1.5 3 4
    eraser 2 3 4
    paper 1.7 3 4
    pen 1.7 3 4
    paper clip 3 3 4

    the point being the “lookup” item eg pen appears in several rows.
    Now when I look it up with a standard lookup in column 2 I get the answer 1.5 (correct)
    When I look it up with a sum of all columns I get 8.5 (correct).

    What I want it to do is look up pen but display the individual results in seperate cells, horizontally (or vertically just in case)

    so the answer would look like
    Pen 1.5, 1.7

    I’m using the table to look up phone numbers (for an IT project) and their corresponding routing info, problem is the numbers appear in the table more than once.

    This is the mess that I tried….no laughing please never heard of a macro let alone an array until an hour ago…..but learning how to name a range was very handy. 🙂 I cribbed it from one of your examples

    =IFERROR(INDEX(pricelist,SMALL(IF(pricelist=”pen”,ROW(pricelist)),ROW(1:1)),2),””)

    Using outlook 2010 and sorry to be difficult but please paste formula answer onto the site, I’ve tried to download some of your other example links to get more ideas and they kept freezing and crashing.

    thanks very much for the help, appreciate it.

    Sam

    Reply

    • Hi Sam,

      There’s an example of lookup and returning multiple values here.

      If you’re downloading the workbooks you need to make sure they’re being saved as the correct file type. Internet Explorer changes file extensions to .zip and you end up with a load of nonsense. To check the file type hover your mouse over the link, this will display the file name and extension in the bottom right or left of your browser window. Then when you save the file type of the .zip with .xlsx or .xlsm accordingly.

      Kind regards,

      Mynda.

      Reply

  29. I have the following formula in cell B1, and it returns the sheet name that the value in cell A1 is located
    {=IF(A1=””,””,INDEX(Mysheets, MATCH(1, COUNTIF(INDIRECT(“‘” & Mysheets &”‘!A:A”), A1), 0)))}
    And the following formula in C1 creates a link to the cell in the respective sheet
    =IF($A1=0,””,HYPERLINK(“#”&CELL(“address”,INDEX(INDIRECT(“‘”&B1&”‘”&”!B:B”),MATCH(A1,INDIRECT(“‘”&B1&”‘”&”!A:A”),0))), “Link”))
    What I would like to be able to do is merge and center cell A1 with A2, and have these same formulas in B2, and C2, but find the first AND second occurrences of value in cell A1 throughout the workbook and in cell c1 there is a link to the first occurrence, and in cell c2 there will be a link to the second occurrence

    Reply

    • I worked around it, by putting an extra column instead of two rows. The extra column instead of searching the named range Mysheets, I made a list of sheets in reverse order, and named a new range Backwards, so it is the first occurence that it finds in D1, but it searched the sheets in reverse order.

      Reply

      • 🙂 very clever, Dustin.

        I noticed in your formulas you are referencing whole columns e.g. A:A and B:B. If you find your workbook starts to slow down on calculation you might want to specify a smaller range e.g. A1:A10000 or less if you can. Referencing whole columns, particularly in array formulas, can be the death of your workbook!

        Kind regards,

        Mynda.

        Reply

  30. Hi Mynda,
    I’m having hard times and can’t figure this one out. I have created a drop down box with a list of names, underneath that cell I created a VLOOKUP for the DOB and under that one another one with an ID #. It does what is supposed to do. But when I copy those cells underneath those to do the same thing the VLOOKUP gives me another rage and not the same range as the previous block of cells. How can I make it do the same?

    Reply

  31. Dear Sir/Madam,

    Please solve my below one problem
    eg:
    column1 column2
    mango 5
    apple 8
    mango 2
    guava 3
    mango 8

    if i want mangoes details then answer should be as below by using only formula:

    mango 5
    mango 2
    mango 8

    i know it will get ans by using pivot table…. but i need to get by any formula.

    Please revert me at the earliest

    Thanks
    Nitin Shinde

    Reply

  32. Hi Mynda, I am working with your instructions ” Excel VLOOKUP to the left using CHOOSE” and it is working fine as long as I am looking up in only one column. However I need to have it work comparing two columns . CanI sqeeze in a second CHOOSE string?
    My issue is that the formula will return the first chosen value that it finds in column E (in your your Example) when the one I want is, say, the second which is matched up with a different partner value in the next column. Any help would be appreciated.

    Thanks,

    Clint

    Reply

    • Hi Clint,

      I think what you are after is this VLOOKUP with multiple values formula paired with the CHOOSE function. So you need to concatenate the two columns you want it to find a match for.

      If you need more help please send me your example file via the help desk.

      Kind regards,

      Mynda.

      Reply

  33. Hello, I really need this formula to work as it would save me so much time but it doesn’t seem to be happening for me. I want to use a regular data range from another tab, but when i do this the value that it returns is just the first cell number in the { } section. So if section was {11,12,13} and row 11 = 100 row 12 = 150 and row 13 = 50. It only returns the value in row 11 (the 100) instead of adding them up (100+150+50 = 300 which is the value i want it to return.

    Here is my formula i currently have in:
    =SUM(VLOOKUP(A434,’BU1 Secured Rev’!$A$4:$M$1624,{11,12,13},FALSE))

    Reply

    • Hi Scott,

      Did you enter your formula as an array formula? That is did you press CTRL, SHIFT and ENTER at the same time? To know if you’ve entered it correctly you can check the formula bar to see that Excel has entered curly brackets at the beginning and end of the formula.

      Kind regards,

      Mynda.

      Reply

  34. I’m looking for a slightly modified version of this and hope you can help. Trying to find the cost of an item based on a part number. Parts come from three different suppliers, each with their own unique number, and these parts are broken out over three columns with the same cost for each (A2:A1360). See below.

    Co1 Co2 Co3 Cost
    K123 1 456 $.07
    K789 2 123 $.50

    On another sheet I have a quote which I want to compare to the data from the first sheet. I would like the formula to reference the part number from the quote (B4 on Sheet 2), look through the three part # columns for a match, and then return the corresponding cost (I4 Sheet 2). Is this possible? I’ve tried several formulas but continue to get a #N/A error.

    Thanks.

    Reply

  35. Hi Mynda,

    I do happen to land on ur website as soon as i search for some problem in google & yes it brings a smile on my face as i know, the problem would be a past now…

    Now my problem is that i have to search for a number from 1 sheet & the same value is there in the other sheet in a cell but there are also few more values in that particular cell entered by alt+enter.

    eg. in sheet1 i in A1 i have
    A
    1 Container No
    2 OOLU8064879
    3 OOLU8017711
    4 CRSU9193709

    where as in sheet 2 i have
    A
    1 “OOLU8017711
    OOLU8831158
    TCNU6627307”
    2 “OOLU8064879
    CAIU8293838”

    So now if i have to lookup for OOLU8064879 in sheet 2 it doesnt show.
    Is there any solution for this one? i am sure u vl suggest 1.

    Reply

  36. Mynda,
    You are doing a great service for us MS Excel users, and making our life simpler with the examples. Thank you and keep up the good work.
    Good Luck

    Reply

  37. This is a very useful tip. But I have another question. Do you have the vlookup formula to lookup a column has repeated labels and return their respective results?

    Col A Col B
    John $20
    Paul $15
    John $30
    John $50

    Want results to look like

    Col A Col B
    John $20
    $30
    $50

    Reply

    • Hi Tony,

      I was asked the same question just the other day. Yes, you can do it, but not with VLOOKUP, but with this array formula:

      =IFERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10="John",ROW($A$1:$A$10)),ROW(1:1)),2),"")

      You need to enter it by pressing CTRL+SHIFT+ENTER.

      Then copy it down your column as many times as required.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply

      • Hi Mynda,

        I am also facing the same problem as Tony with multiple values to the same label.

        Col A Col B

        Plan1 Offer1
        Plan2 Offer2
        Plan1 Offer3
        Plan1 Offer4

        Want results to look like

        Col A Col B

        Plan1 Offer1
        Offer3
        Offer4

        Do you have another formula please ??

        Thanks and Best Regards,

        Reply

        • Hi Aoun,
          Why do you need another formula? The data you presented is in exactly same structure as Tony’s data, so his formula should work with your data too:

          =IFERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10="Plan1",ROW($A$1:$A$10)),ROW(1:1)),2),"")

          , confirmed with Ctrl+Shift+Enter, as it’s an array formula.
          Let me know if you succeeded to apply the formula. If you need more help on this, you can upload a sample of your data on Help Desk
          Catalin

          Reply

  38. I need help with my lookups if possible. I’ve tried everything, but i can’t wrap my head around the logic.
    I need my formula to show lookup using two or more criteria.
    I want my formula to show the maximum value in column A, where the corresponding value in column B =1.
    E.g.
    A B
    1 1
    2 0
    6 0
    2 1
    3 1
    12 0

    I want formula to show number 3, as it is the max value in A where B = 1.

    Reply

    • Hi Cam,

      You need the MAX IF Function…except there isn’t one :), but you can create it using this array formula:

      =MAX(IF((B1:B6=1)*(A1:A6),(A1:A6)))
      Entered with CTRL+SHIFT+ENTER

      You can read this tutorial on the MAX IF Function for an explanation of how it works.

      Kind regards,

      Mynda.

      Reply

  39. Hello!
    Thank you for your great site & info.!
    I am trying to use “lookup” function in Excell 2007 to match data in three different files. The program says I have too many arguments in the formula I have written (below). Please let me know if there is a better way to do what I am looking for.
    =IFERROR(LOOKUP(H2,'[2002LoadedNetwork.xlsx]2002LoadedNetwork’!$A$2:$FE$27744,21,FALSE),LOOKUP(G2,[highway.xlsx]highway!$A$2:$AR$30744,39,FALSE)
    Thank you!
    Have a great day.
    Safieh

    Reply

    • Hi Safieh,

      What do you mean by ‘match’ and what are you trying to return? A single result, a sum of multiple values?

      Your formula above is performing two LOOKUP’s but you haven’t told it what to do the with the result of those two lookups. Do you want them both displayed in once cell, added together, averaged etc?

      Please give me an example of what you are trying to do.

      Kind regards,

      Mynda.

      Reply

  40. Hi Mynda

    I have a sheet and I want to check in a table on a if the person is trained, and if they are then the cell should show “SB”. This works fine except some cells get #N/A. Please would you tell me how to use the ISNA function in my formula to take care of this error?

    =IF(VLOOKUP(M4,Lists!$H$2:$K$38,3,)=0,””,”SB”)

    Thanks
    Simon

    Reply

    • Hi Simon,

      If you’ve got Excel 2007 or 2010 then you should use the new IFERROR function to handle #N/A errors. The old IF ISNA combination requires Excel to calculate the VLOOKUP twice, where as the new IFERROR is much more efficient.

      =IFERROR(IF(VLOOKUP(M4,Lists!$H$2:$K$38,3,)=0,””,”SB”),””)

      If you only have Excel 2003 you can use the IF ISNA like this:

      =IF(ISNA(VLOOKUP(M4,Lists!$H$2:$K$38,3,)),””,IF(VLOOKUP(M4,Lists!$H$2:$K$38,3,)=0,””,”SB”))

      Kind regards,

      Mynda.

      Reply

      • Hi Mynda
        Thanks so much, I wasn’t sure about the nesting and this works a treat! I have version 2003 here at work, so used the second solution. The 2207 version is much more straight forward and I can use this on my home pc. Once again thank you.

        Best wishes
        Simon

        Reply

  41. Hi Mynda

    Thanks for getting back so quickly!

    Had some success, but getting #N/A error

    =IF(MATCH(B4,$E$4:$G$4,0),\Found\,\Not Found\)

    B C D E F G
    4 Sam Found Pete Sam Shaun
    5 Shaun Amy Pete

    Sam #N/A Pete Shaun
    Shaun Amy Pete

    Not sure why?

    Regards
    Simon

    Reply

    • Sorry that did not come out like I typed:
      =IF(MATCH(B4,$E$4:$G$4,0),”Found”,”Not Found”)

      If I type Sam into the table, it shows Found, if it is not in the list I get the #N/A

      Regards
      Simon

      Reply

      • Hi Simon,

        Ah, sorry. In my haste I didn’t check the ‘Not found’ scenario worked.

        Here you go:

        =IF(ISNA(MATCH(B4,$E$4:$G$4,0)),”Not Found”,IF(MATCH(B4,$E$4:$G$4,0),”Found”))

        You may need to enter the double quotes in again because the display in italics on the web page and Excel doesn’t like them like that.

        Kind regards,

        Mynda.

        Reply

  42. Thanks, looks like this is what I am looking for but not getting the desired result!
    Please help, what am I doing wrong…? I am working on a bigger spreadsheet but have made a small one to test to see if this works before using in the main one.
    I am trying to look up a value in a cell (a person’s name) and want to know if it appears in the table. The person’s name is in cell B4 and the table is E4:G10 and I entered the following array formula (using crtl>shift>enter) {=ISNA(VLOOKUP(B4,E4:G10,{1,2,3},FALSE))}

    It finds the name if it appears in the first column (ie E4:E10) but not if it is in any of the others (i.e F4:G10)

    best wishes
    Simon

    Reply

    • Hi Simon,

      Since you don’t want to return a corresponding value once you find the name you can simply use the MATCH function with the IF function to tell you if the name exists in your table. Like this:

      =IF(MATCH(B4,$E$4:$G$4,0),”Found”,”Not Found”)

      This will return the text ‘Found’ or ‘Not Found’ depending on the outcome.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply

    • Hi Darren,

      Yes you could modify it to work on multiple sheets. e.g.

      =SUM(VLOOKUP(B3,Table1[[Name]:[Pay Rise 2006]],{4,5,6},FALSE),SUM(VLOOKUP(B3,Table2[[Name]:[Pay Rise 2006]],{4,5,6},FALSE)))

      Where Table1 is on Sheet1 and Table2 is on Sheet2 and so on.

      Note: Don’t forget to press CTRL+SHIFT+ENTER to enter it as an array formula.

      Kind regards,

      Mynda.

      Reply

  45. Its Cool… I was doing all the time 3 Vlookup for the same, now i got the Idea, how to do it. I’m impressed, Great Tips!!!

    Reply

  46. Thanks for the tip Mynda, just what i was looking for. I keep getting a #REF! error when a value is in the range, i will continue to play around and modify 😉

    Thanks again!

    Reply

  47. Thank you Mynda for all your tips – I need to run through several of your online training modules. In particular, I always seem to have issues with the VLOOKUP features in Excel – thanks for all your tips!

    Reply

Trackbacks

YouTube Channel Follow us on Facebook Twitter Follow Google+ RSS Feed or Email

Blog Categories

Resources

Excel Resources

Affiliate Program

Become an Affiliate
Secured by Sucuri Rect
✕
Hang On, Don't Go Just Yet.
Excel Tips & Tricks Book

As a thank you for visiting how would you like a
10% Discount Code to use with any of my courses?

I'll also give you a copy of my eBook packed with
100 Excel Tips & Tricks.

And I'll send you my weekly Excel blog posts.

Enter your email address below to receive all of these.

Regards, Mynda (Excel MVP)

mynda treacy mvp
100% Privacy. We don't spam.
✕
SUBSCRIBE AND LEARN EXCEL
Excel Tips & Tricks Book


Learn great Excel features, tips and tricks each week from my newsletter.

Just enter your email below, and as an extra bonus, I'll send you a copy of my '100 Excel Tips & Tricks eBook' - for nothing!

Thanks,

Mynda

 



100% Privacy. We don't spam.
✕
100 FREE EXCEL TIPS & TRICKS
Excel Tips & Tricks Book


Want to learn more great Excel tips and features?

Enter your email below and I'll send you my weekly blog post.

Plus, by signing up you'll get a copy of my '100 Excel Tips & Tricks eBook' - for nothing!




 

100% Privacy. We don't spam.