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 3. Total comments: 188

  1. 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.

  2. 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?

  3. 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.

  4. 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.

  5. 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?

  6. 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.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)