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 8. Total comments: 342

  1. i want to extract two unique different column of data in to one cell from another sheet after searching through an array
    a b c(ans)
    1 4 2
    2 3 1
    3 4 2
    4 2 4

  2. Awesome tutorial, thanks a lot!

  3. Hello Svetlana,

    The issue I experienced not pulling the data was related to #N/A (error) in couple of cells. Once I replaced these values all worked fine.

  4. Sorry for the formatting above, but it only became so after I posted the message. There are currently 7 columns, one with the A/A numbers and another 6 with data. A,B,C,D,E and F are the head titles of the 6 columns with the data.

  5. Hello Svetlana.

    I have the following table. Please have a look at my question following the table.

    A B C D E F
    1 EXCB11 EXCB10 0.202 0.79 0.159 0.159
    2 EXCB10 EXCB09 0.117 0.72 0.084 0.244
    3 EXCB09 EXCB08 0.042 0.61 0.026 0.269
    4 EXCB08 EXSTMH05 0.016 0.2 0.273
    5 EXSTMH07 EXSTMH09 0.016 0.5
    6 EXSTMH08 EXSTMH07 ? ?
    7 NEWCB12 NEWSTMH04 0.152 0.76 0.115
    8 NEWSTM EXSTMH05 0.178 0.76 0.250
    9 NEWCB14 NEWSTMH15 0.093 0.83 0.078
    10 NEWSTMH15 NEWSTMH13 0.000 0.92
    11 NEWSTMH13 EXSTMH08 0.000 0.76
    12 EXSTMH0 EXSTMH05 0.019 0.59 0.089
    13 EXSTMH05 TMNT DEVICE 0.000 0.59
    14 TMNT DEVICE EXMH01 0.000 0.87 0.000

    - For every cell of column A, I am trying to find the cells in Column B that have the same value with it. For example in the given table for Cell A13 that has the value EXSTMH05, I would like to find cells B4, B8 and B12 that also have the value EXSTMH05.
    - Then, for each one of the cells that I found I want to get the values from the respective cell of Column F and sum them up. So, in the given example I would like to get the values from F4, F8 and F12 and find their sum. The result I am seeking in this example 0.273+0.250+0.089.

    Can you help me? I have tried using various formulas but to no result.

  6. Hi Svetlana,

    I wanted to ask you for your help with my table:
    https://we.tl/mMztWNG1lY

    What I would like to display in Sheet 2, is the quantity of those topics, which meet the chosen criteria and also display all corresponding texts, links and clicks, one below the other, taking in account the two criteria chosen at the top: "Category" and "Language".

    What we also have to take in account is that the categories, texts, links and clicks can be found in several columns and not just in one.

    I really hope that you can help me with this issue. :)

    Best regards,
    Sanjin

  7. Svetlana,

    Spasiba, for your quick response. Everything is working well even on large volume of data. When the data is aligned well n-1 type of configuration it works well.

    As for the "relative reference" I meant relative position. I tried multiple examples, the all worked if properly setup.

    Cheers,

    Sam

  8. Hello,
    I'm hitting a unique error with this formula. I have the formula on Sheet2, and am trying to pull the information from Sheet1, A4, titled "Expirations". In the "IF" statement, if I put the reference item to match the item in the first row of the table on sheet 1 (89), it pulls the information from A4 over after I hit Enter. If I change it match the item in row 2 (229), it doesn't pull the associated info from A5. Also, if I hit ctrl+shft+enter to use this as an array formula, it doesn't pull any results.

    My ultimate goal is to pull over all of the rows that have a negative number in Column E, so if you can offer any guidance towards that, that would be awesome. :) But in the meantime, helping me understand why the array formula isn't working between worksheets, and why it only pulls over the first item when used as a standard formula, would be awesome. The formula I'm using is:

    =IFERROR(INDEX(Expirations!A$4:A$2864, SMALL(IF(89=Expirations!$E$4:$E$2864,ROW(Expirations!A$4:A$2864)-3,""), ROW()-3)),"")

    Thank you!
    Kevin

  9. Svetlana.
    Great work. The formula works well even in Two workbooks format one for data and one for matching. Also worked when replacing relative values with Named Ranges. I extended the example to about 6000 rows, still works. Note the slight change with named ranges "D$2=Sellers, Row(Seller)" Using Row(Fruits) does not return results.

    =IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")

    =IFERROR(INDEX(Fruits, SMALL(IF(D$2=Sellers, ROW(Sellers)-2,""), ROW()-2)),"")

    My trouble is, I have a very large data set (about 20K+ rows) and I get only the first value repeating for the entire array in my real work. I can't figure out where am I going wrong.

    1. Hi Sam,

      Sorry, I do not quite understand the part about relative references. In the original formula, all ranges are fixed (absolute references), and in named ranges the references are also absolute by default. I've done the same replacement in the sample workbook, and it works just fine. Please see N3:Q5 in sheet "Return matches in columns". The updated file can be downloaded here.

  10. Hi Svetlana Cheusheva, in your first formula with index and Row-2 example above. I do scores for softball league and I need to look up the teams identifier (e.g. 10a) and return the date of the game. However, the column range I need to look in houses the matchups for that day's games. e.g. 10a v 10b in a cell. Can I use wildcards in the array formula (e.g. *10a*) as my look up value vs just 10a. I tried doing "*10a*", but got a Num error (took out the iferror to see why blank). Do I have to input "*"&D3(being 10a)&"*"=C2:c27, etc.??? Any guidance here is much appreciated!

  11. Wow! very well written and expressed. Exactly what I wanted the first formula itself. Thank you!

  12. i want horizontal date range Vlookup pls. provide solution

  13. This is a great help but how do I return a value of more than one cell?

    Such as if there were two more columns that had the Price and Market. I would want the result like this in individual cells:

    Adam
    Banana $2.99 Smith's
    Oranges $1.99 Von's
    Lemons $.99 Joe's

    I would also like to know if I had Columns A to M but only want to return with results from Column A, B, C, G. How would I do that?

    Thanks in advance!

  14. My data file has multiple records against one item..
    Can you please help, if i type the item in a particular cell, all multiple values should appear in front of item e.g
    Part Number Supplier Lead Time Price $
    Orange D5396 45 25.00$
    Orange A6545 50 26.00$
    Orange C9256 60 25.50$
    Orange A6498 45 30.00$
    Orange Z9655 40 26.50$
    Orange D5632 90 29.75$
    Mango W5696 120 5.25$
    Mango A6536 125 4.75$
    i need help, if i type part number in a particular cell, all related information should appear against it...

  15. hye i got two month of data that need to be validate to the latest TotalValue and ignore that already used in the next day.

    i want the outcome like this
    A B
    Unit
    14200001 78
    14200002 144

    and so on.

    the data on my excel

    A B C D
    Unit Date TotalValue Used
    14200001 15-01-18 78 77
    14200001 16-01-18 1 1
    14200001 15-02-18 78 78
    14200002 15-01-18 143 142
    14200002 16-01-18 1 1
    14200002 15-02-18 144 144
    14200003 15-01-18 161 161
    14200003 15-02-18 161 161
    14200004 15-01-18 150 150
    14200004 15-02-18 150 150
    14200005 15-01-18 158 158
    14200005 15-02-18 158 158
    14200006 15-01-18 157 157
    14200006 15-02-18 159 159
    14200007 25-01-18 104 104
    14200007 25-02-18 104 0
    14200007 26-02-18 104 104

  16. =(IFERROR(INDEX(Schedules!$A$5:$A$310; SMALL(IF(B$1=Schedules!$C$5:$C$310;ROW(Schedules!$A$5:$A$310)-4;""); ROW()-22));""))

    I need help.
    I'm using this formula. I need to search hours at collum C to get names from collum A.
    The result i get is blanck from the iferror formula, but if i do F9 it gives me the names. In this case i need to do one by one instead of it be automatic.
    I can't figure it out what is wrong.

    Thanks for the time

  17. Can I use this in a Data Validation?
    =IFERROR(INDEX(sTurrets; SMALL(IF(A$3= sTank_id_turret; ROW(sTurrets)-2;""); ROW()-2));""). I am using named ranges from another sheet as the return_range the lookup_range.
    I have also tried =INDEX(sTurrets;MATCH(A3;sTank_id_turret;0)) and
    =VLOOKUP(A3;allTurrets;3;0).
    Learned these from these and other pages here.
    The index one works but only gives me 1 value, while there are 3 to 4 values to return.
    The IFERROR says List source must be delimited list when I place it in the data validation.
    Please help.

  18. I need to the formula to filter data in column very easy and lookup conditions thank you so much for your support

  19. Hello,
    This works fine if the vlook up value is an Exact match. Is there any way to get all partial matches?

  20. DOES THIS FORMULA WORK IN GOOGLE SHEETS?

  21. Really appreciate how you have taken the pains to elaborate on the logic and the break up of the formula and brilliantly at that, too. Very helpful...thanks!

  22. Hello,

    I'm having a hard time finding a formula to use to setup a conditional format. I have one close, but not extensive enough. I have a formula in my conditional format rule where when a row has a cell with the "F" value, =C$3="F", that when applied to a range, =$C$4:$HU$11, will make any column in the range gray (that is the column with an "F" just above it will be gray). Now I need create a similar setup where if in the same range, =$C$4:$HU$11, I have any column in that range(for example), C4:C11, having more than on "C" value in a cell, to highlight that row in yellow.

  23. Thanks for this help with array formulas.
    I need some assistance displaying the data. I need to display it in a Gant Chart Format.
    For Explanation: Jan1 Apples, Jan2 Oranges, Jan3 Pears, Jan4 Oranges, Jan5 Pears, Jan6 Oranges….
    I need to display the Dates across columns and the fruits in rows and populate the cells where the fruit and dates align.
    The Array Formula will pull the dates but I cannot figure out how to populate the “Gant Chart”

  24. Hi,

    I have a spreadsheet which tracks defects.

    I have a LOOKUP to determine a 'severity' based on whether the item is either "High" or "Low" and the length of the defect (input as a number).

    I then have a seperate table which lists the action code to be applied, based on a combination of "High" or "Low" and the 'severity'.

    I would like Excel to automatically return the action code from a remote table (in the same worksheet), based on the values in the two seperate cells.

    So, cell A1 is either "High" or "Low".
    The operator inputs a numeric value in cell A2.
    Based on the values in A1 and A2, a LOOKUP returns a 'severity' in cell A3.

    What should then happen, is a value for 'Defect Code' should appear in cell A4 based on the combination returned in cells A1 and A3.

    Any ideas would be greatly appreciated.

  25. Thanks for this formula. I've been looking for this however it's been showing up as an error on my end, saying "Did not find value '43096' in VLOOKUP evaluation."

  26. Hi.

    Thank you for this, it has saved me a ton of time.
    I did have a few problems which I've solved.
    In your formula you've used ",", while in my excel it worked with ";".
    Also, the formula couldn't find matches if formatting was different. E.g., my row was formatted as numbers, while my column was text, I think.

    1. Hi Muhamed,

      The use of comma or semicolon for separating a formula's arguments is dependent on which character is set as the List Separator in your Regional Settings. For example, in North America the default list separator is a comma, while in Europe it's semicolon. As for the second issue, you are right, numbers formatted as text could cause problems.

      1. thank so much it very helpful.

  27. It is working. Its result is awesome.
    Thanks

  28. Hello sir,
    I have used several time, Formula 3. Vlookup multiple matches based on multiple conditions. But it is not working. It is not showing anything in the cell. By copying the formula in another it also not respond.
    pls help it need to do this.

    1. Download her template and copy formula from there (look through different sheets) and be sure to adjust formula for your data.
      If you are working with numbers make sure formatting of the cells is the same...

  29. I have report of dispatch as like, Gate Pass No's in Rows & against that Invoice No's are in multiple columns. How can I create pivot for particular Gate pass No having list of Invoices in rows, instead of column.

  30. Hi,
    I I want to compare between to columns and list the new value in the second row;
    col1 col2 col3 (result)
    abc abc www
    adc www
    ere ere
    adc

    1. Hello,

      Please try the following formula:

      =IFERROR(INDEX(B:B,SMALL(IF(B:B<>A:A,ROW(B:B)),ROW(A1))),"")

      Please note that this is an array formula. You should enter this formula into the first cell in column C, hit Ctrl + Shift + Enter to complete it and copy the formula down along the column. Just select the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) down.

      Hope it will help you.

  31. Hi, I have 48 sheets and I need to copy the data ( Don't need to sum up or count)from these 48 sheets to another sheet based on a condition. Eg: I need to copy complete rows wherever one of the column names is "ABC". There are multiple matching rows in the single sheet. So I cant use VLOOK up.As I don't know VBA, I find it very difficult, Can anyone help?

    1. Hello,

      Please try to solve your task with the help of the Combine Sheets tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
      After you install the product, you will find Combine Sheets in the Merge section under the Ablebits Data tab.

      Hope this will help you with your task.

  32. Dear concern,
    Flowing function is best & work properly. But i need a little query, please help me. I want to increase the table range (300 instead of 30). When I increase it, but it does not work. please help me.

    =IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E$3=$A$3:$A$30))*(--($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), ROW()-2)),"")

    1. Is it working on 30? If it does it should work on 300 as well...

  33. Hi i have a list of herbs (67 total) that each have three different effects i want to display a list of items that have the same effect in any of of the 3 effect column.
    Column A Column B Column C Column D
    eg Acerba Moretum Raise Will Raise Agi nothing
    Adipem Nebulo Raise Str Lower Will Damage HP
    Albus Viduae nothing Raise Str Raise Will
    etc through all 67 herbs

    searching for Raise Will, displays Acerba Moretum then next row Albus Viduae
    as both have that effect in one of its traits

    Thanks in advance

    1. Hello,

      Please try the following formula:

      =IFERROR(INDEX(A:A,SMALL(IF(((B:B="Raise Will")+(C:C="Raise Will")+(D:D="Raise Will")),ROW(A:A)),ROW(A1)),1),"")

      Please note that this is an array formula. You should enter this formula into the first cell in column E, hit Ctrl + Shift + Enter to complete it and copy the formula down along the column. Just select the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) down.

      Hope it will help you.

  34. Hi,

    I am new to excel Vlookup. I wanted to do a lookup for two criteria (which I have place a vlookup formula) that linked to a spinner command button.

    My intention is as the spinner command button changes the criteria change, so does the intended lookup results.

    Thanks in advance.

  35. I have two column table-name, column-name on an excel sheet and other excel sheet has table-name,column-name and identity-column. I have to get all the identity-column to the first excel sheet if table-name and column-name are matched. Can anyone help me with this. Thanks in advance.

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source 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.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  36. Hi there,

    Is there a way for the "return range" to lookup another excel sheet within the same workbook? I want to use Formula 1: Vlookup multiple values and return results in a column.

    Thanks!

    1. Hi Kieran,

      Of course, there is a way. Just put the worksheet name followed by an exclamation mark before the range, like Sheet2!A1:A10

      If the worksheet name contains spaces or non-alphabetical characters, enclose it in single quotation marks, e.g. 'Sales report'!A1:A10

      Technically, this is called an external reference, and you can find more details about it here: How to create external reference in Excel to refer to another sheet.

  37. Hello,

    I am having issues trying to do a lookup based on multiple conditions.
    Is there any way for me to get direct assistance on the file that I am working on?
    I have tried to imitate results the with sample worksheet and still could not achieve the correct results.

    What does pressing 'Ctrl+Shift+Enter'do? I don't understand that part of the section either.

  38. Thank You very much. I've ported this formula to google sheets and works flawlessly, Best.

  39. highlight range where you would like input to be place, Press F2, then press Control/Shift/Enter simultaneously. It is an array formula. Hope it helps

  40. The formulas are working perfect in their calculations, but they would NOT write the results into my cells! Like, I select the whole formula, hit F9, and it will show what I want...but it will keep entering into the cell the ERROR option from the IFERROR function. I don't get it. I am comparing values on a different sheet and pulling values from that sheet to a different sheet. My formula looks like this:

    =IFERROR(INDEX('Career Stats'!$C$3:$C$53,SMALL(IF(L$3='Career Stats'!$I$3:$I$53,ROW('Career Stats'!$C$3:$C$53)-2,""),ROW()-4)),"")

    Any suggestion to actually have it write what I want? Thanks.

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

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

  43. 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!

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

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

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

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

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

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

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

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 :)