Comments on: How to Vlookup multiple values in Excel with criteria

When using Microsoft Excel for data analysis, you may often find yourself in situations when you need to get all matches based on a specific id, name, email address or some other unique identifier. An immediate solution that comes to mind is using the Excel VLOOKUP function, but the problem is that it can only return a single match. Continue reading →

Comments page 10. Total comments: 342

  1. Hello!

    Thank you for the help.

    I have used the multiple search formula successfully but I have numerous results I intend to display within a limited page space - statement format. Over 100 results to display within a page that holds only 20 results, hence 5 pages of results to display. The goal is to create a statement with a button or toggle to switch between the various pages, clearing previous results and continuing to display more results accordingly.

    Much appreciated.

      1. Thank you, Irina.

  2. Hello!

    I need to know some things if someone help me I be very thankful.

    1. I need to sort marge cells in single column.
    2. I need to add subtotal after sorting marge column.
    3. I need to find different values on multiple sheet using single identifier (means I use vlookup for adding data in multiple sheets and every sheet has different numerical value of each identifier. I want to show that value when I enter identifier in each sheet from whole workbook.)
    There is no value in workbook when I did not enter any identifier in that workbook.

    I am using vlookup formula with IFERROR and some conditional formating cells; data data for vlookup is from another workbook.

    Many Thanks.

    1. Hi Khurram,
      Your first condition (if I understood correctly, it is 'to sort merged cells in single column') cannot be fulfilled, Excel does not sort merged cells.
      So first of all, try to avoid merging cells or find another solution and only after that apply sorting.

  3. Hi,

    I tried to follow along but it is not working for my spreadsheet. I am currently using the below formula in column AT but it is not working:

    =INDEX($BA$2:$BA$10000,(IF(1=((--($AI2=$AX$2:$AX$10000))*(--($AP2=$AY$2:$AY$10000))*(--($AQ2=$AZ$2:$AZ$10000))),ROW($BA$2:$BA$10000)-1,"")))

    I have data in columns AX, AY, AZ, and BA. This data goes from row 2-row 100 (so AX2-AX100 has info, etc). I also have data in columns AI, AP, and AQ. If the data in AI, AP, and AQ matches AX, AY, and AZ, then I want AT to generate the number that is in BA.

    Example of what my excel looks like:

    I need a formula in AT:

    Row 1: AI AP AQ AT AX AY AZ BA

    Row 2: 123 BBB SS 124 CCC TT 1001
    Row 3: 124 CCC TT 125 DDD UU 1002
    Row 4: 125 DDD UU 126 FFF WW 1003
    Row 5: 0 EEE VV 123 BBB SS 1004
    Row 6: 126 FFF WW 0 EEE VV 1005

    I am hoping the formula in AT2 would make 1004.

    Thank you!

    1. Hi,

      please try the formula below:
      =INDEX($BA$2:$BA$10000,SMALL(IF(($AX$2:$AX$10000)&($AY$2:$AY$10000)&($AZ$2:$AZ$10000)=($AI2)&($AP2)&($AQ2),ROW($BA$2:$BA$10000)-1),1))

      Please note that this is an array formula that should be entered by pressing Ctrl+Shift+Enter on your keyboard.

      Hope this solves your task!

  4. I have been working on a formula for a couple of months now, and even after asking several people, no one knows the answer. I'm thinking I may need to use INDEX, but if that is the case I can't get it to work. I have a spreadsheet with items that have their names listed 3 times in column A. In Column B I have 3 different sizes for each name. For example, A2 says Apple, B2 says 16, A3 says pear, B3 says 32, etc. I need the formula to calculate the quantity in column C for each item name and size respectively. I have managed to get it to calculate the quantity for the size 16 (in oz.) for the apple, for instance, but it adds that quantity for every single 16 on the spreadsheet, and I need the to add separately. I think it may be because I have each item name listed multiple times with the same size, but I would think there would be a way around this. Can anyone help if that's not to confusing? Thanks!

    1. I'm sorry, it should say A3 says Apple in my example instead of pear.

      1. Hello,

        For us to help you better, please send us your workbook with the data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.
        Please include the link to this comment to the email.

        We'll look into your task and try to help.

  5. Inside the IF, why do you need ROW($B$3:$B$13)-2 ? Why not just write 1?

    1. Hi Manrique,

      Because we are dealing with an array formula, ROW($B$3:$B$13)-2 returns not just 1, but an array like {1;2;3;4;5;6;7;8;9;10;11} where each number is a relative position of a row in the specified range. We subtract 2 because our first cell is in row 3, while its relative position in the range is 1. The above array goes to the value_if_true argument of the IF function. IF compares the lookup value (D2) with each value in the lookup range (A3:A13), and if the match if found, returns the relative position of the row; an empty string ("") otherwise.

  6. Hi, Could you please help me

    In column A i have list of entities like Row1)coco-cola, Row2)AT&T, Row3) CSC and so on..... and in column B i have list of Index names like CDX-EM-27v1, CDX-EM-Diversified, etc. one entity could be a part of more than 2 indexes. is there any way i can get the names of the indexes against each entity in column C

  7. The formula works for me but after few columns it stops bringing more values. I have 22 values in my table but it only pulls up to 12 values.

  8. Could you please suggest a formula for Multiple values in on column?

  9. You can use offset instead of index. Specially when working with large sets of data.

  10. This is exactly what I need, but I can't get it to work
    In one tab I have Recipes
    i.e Cottage Pie Mince Meat
    Veg
    Potatoes
    In the other tab I have what to order

    I want it to look up the recipe title and pull through the full list of ingredient but it is completely rejecting the formula

    Can you help please?
    This is as far as I got
    iferror(index('WHAT TO ORDER'!A:A,small(if(1(--(RECIEPES!D:D))*(--('WHAT TO ORDER'!A:A)),row(RECIEPES!H:H)-2,""),row()-2,"")))

    1. Specify your Column. Ex. A1:A1000.

  11. Hello I need Help, its working if the reference data is in the same sheet. I have 60000+ values columns. So i was using it in the different sheet then your formula is not working.

    Below is the formula i was using. My 'Seller' Name is in F2 and total reference data is in Sheet 9

    =IFERROR(INDEX(Sheet9!B:B, SMALL(IF(F$2=Sheet9!A:A, ROW(Sheet9!B:B)-2,""), ROW()-2)),"")

    1. Does your reference data in sheet 9 starts row 1?

  12. I typed it all in and when i check it wil F9 it gives the answer I expect, but when I hit return it gives me a #value error. Do you think it is because I am referencing another worksheet for the if condition?

  13. Svetlana,

    This is a great tool, thank you for the wonderful instructions. I've been trying for a while now to do a partial vlookup using these formula's but have had no luck. Do you know why the following doesn't work? Is there a way to accomplish this with your formula?

    IFERROR(INDEX( return_range, SMALL(IF( lookup_value & "*" = lookup_range, ROW( return_range) - m, ""), COLUMN() - n)), "")

    1. I think the problem is "*". It become the concatenated lookup value and an asterisk.

  14. Hi,

    How can you modify the Formula 1 for me if the products names are repeating

    Thanks

  15. Hi i was woundering if anybody could help. i have read the above and many more treads i have a formula that works, i just dont now how to expand the array arcoss muilple colums.

    The formula that works =INDEX(B2:B40,(MATCH(E1,J2:J46,0)))

    something i dont think is possible but i very much need to work.

    =INDEX(B2:B40,(MATCH(E1,J2:U46,0)))

    have tried muilple varations creating a helper colunm and concatenating J2 to U2 into one colunm but even that dosnt work

    can anybody help

    cheers

    1. match does not work for multiple columns.

  16. Thanks, u solved my big problem of my work in office

  17. Hi.

    Is it possiple to do the lookup with multiple values in a collum from an external file?

  18. Hi Svetlana,

    I try the formula "=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")" but didn't work it brings back "#VALUE!, I copied the exact data from the sample same cell positions but didn't work, could you please help me?

  19. Hello, I need some help...

    I tried the formula and ajusted it to my data, and it worked for the cell in which I wrote the formula. However, when I try to drag the formula it doesn't work.

    1. Hello, Mafer,

      you're probably experiencing problems because of the cell references. Read this article, to learn more about absolute and relative cell references.
      If this doesn't help, send us an example of the workbook with your source data and the result you expect to get to support@ablebits.com.

  20. Hi I need some help

    I have two sheets, one is where the data is being pulled from and the other is where the data is being manipulated. In my data set I have employee numbers that have job titles along with the store numbers.

    In the sheet where my data is being manipulated I would like whenever to display all the employees that belong to a particular store and their job titles please help

    1. Hi, Oscar,
      to return different pieces of info, you will need to create two columns: one for the employees' numbers and another for their job titles. And each column will require its own formula. Then, use the 1st VLOOKUP formula of this article to return results in a column.

      1. Hi,

        I'm pulling data from a main excel located on the web. I have created a spreadsheet that will pull data from the main spreadsheet. I did this to be able to reorganize the data and remove the blank cells that appear in the main spreadsheet on the web. Well, in the spreadsheet that I created, the data is being pulled but it is not in a list format. I still have blank cells between the data. What should I do?

  21. Very Informative, Great learning. Thank you :)

  22. Dept. dr.name opdno. Opdno1
    Gastro A. 2.
    Gastro B. 5.
    Gastro total. 7.
    Ent. D. 8
    How index match will be used in column opd no1 in row Ent so that 8 will be fetched 4th column 5th row.

  23. Excel makes life easy

  24. Awesome, incredibly fantastic

  25. Hi. This looks to be a source of great help. I am looking for a solution of my below mentioned problem;

    I have a Table 1 in which there are two inputs. 1) Cable Diameter (mentioned in a Column) 2) Max. length per Drum Type (mentioned in different columns in front of respective row of the diameter). I need to fetch the drum type for combination of my inputs of Cable diameter and Max Length.

    I hope I am clear in my description of the issue.

    Thanks & Regards,
    Salman

  26. thanks a lot

    very best activity to teach vlookup.

    regards

  27. I already copy this formula "=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")", but it didn't work. Can you help me?

    1. Hello Nawanto,

      Did you adjust the formula for your data? Please read carefully the "How this formula works" section to understand the formula's logic and how to customize it for your own data.

      1. Hello Svetlana,

        I have sheet 1 and 2
        In sheet 1, there are col. A, B and C . Col A, B and C have multiple duplicates values, Col C is date

        In Sheet 2, there are Col A, B, C and D. Col A, B, C and D also has multiple duplicates values, C is date
        I want value from Col. D in sheet 1 (not sure how to use lookup functions)
        Only lookup reference I have is Col. A and B in Sheet 1 and 2.

        Problem: there are multiple values in both sheets for col. A and B.
        Can you please let me know the solution?

      2. Hello Svetlana,

        I have same error when I use your formula in your sample worksheet. Its for the "Formula 3. Vlookup multiple matches based on multiple conditions". It seems when I open your worksheet it shows me the result but when I double click the cell that contains the formula and after seeing it I pressed enter and it shows a blank cell which is the other result of IFERROR formula. Then I dig deeper of where the formula have error and found out the Range "(--($E$3=$A$3:$A$30)) * (--($F$3=$B$3:$B$30))" has error. The range $A$3:$A30 and $B$3:$B$30 have a value of "#VALUE" which means the range does not return any value.

        I was wondering how can we compare a single cell value (E3 or F3 to a range of values). That is why Excel returning no value and we get error.
        If you have any suggestion on how to tackle this issue it would be helpful.

        I'm using Excel 2013.

        Regards,
        Raj

        1. Nevermind Svetlana,

          I pressed Ctrl+Shift+Enter and the formula works just fine.

          Now I just need to know why pressing enter gives error and pressing ctrl+shift+enter gives the right answer. I know it has to do with array formula but why it is perceived by excel in different way.

          Could you please clarify?

          Regards,

          Raj

          1. Hi Raj,

            Unlike a regular formula, an array formula evaluates all individual values in an array and performs multiple calculations according to the conditions expressed in the formula. And to let Excel know you are entering an array formula, you press Ctrl+Shift+Enter. It's as simple as that :)

            For the detailed explanation of array formulas, please see the following tutorials:

            Excel array formulas, functions and constants - examples and guidelines
            Excel array formula examples for beginners and advanced users

            1. Awesome explanation, Thanks alot.
              Could you please support and guide, I have two sheets with product name are same in both sheets but values are different in both, how to V LOOKUP both sheets to retrieve all values in a row for one individual product.

      3. I've tried the exact same data set however, the formula itself doesn't work.

        1. Hi Alex,

          You can download the sample worksheet here and make sure the formula does work.

          1. downloaded your source file from the link....

            when i run the formula, nothing is displayed, although the formula pane calculated the correct result..

            how to display the formula result on the table?

            1. @Uchay Hey buddy, have you tried to activate the index formula by press ctrl+shift+enter when you're on the formula. It should help extract the value instead of a blank cell because there's an error covered by the formula.

              Good luck.

  28. Great ideas,I'd been trying since 2 years to build a formula for same conditions..thanks a lot.

  29. god gives you every thing you wish

  30. Great info!! Another combination I like to use for pulling info in based on a common ID across the two sets is: = INDEX( , MATCH( , ,0))

  31. Awesome:-), Svetlana. Carry on.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)