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 2. Total comments: 191

  1. Hi, i have a question; i have 2 coloumns

    A B
    a x
    b y
    c z
    a, b z, x
    c, a y, z

    How can i get results as;

    a x z, x y, z
    b y z, x
    c z y, z

    ??
    Pls help

      1. Actually it's like
        =IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")
        formula but i have to specify IF($D3=$A$3:$A$13 section as "look in coloumn A, whichever cell contains spesific text this is the reference one, continue formula" i cannot solve this

      2. Thank you very much, i willl read them.

  2. Hi, I want to Check something 1000 of Restricated Words From 1 line of Discription(200-300)Alphabet .Is there's any Formula to do that Please help Me in this. Thank you in advance

    1. Hello!
      If you want to know if at least one word from the list occurs in a text, use the formula:

      =SUM(IFERROR(SEARCH(B1:B1000,A1),0))>0

      If you want to specify which words are found, try to enter the following formula in cell С1 and then copy it down along the column:

      =ISNUMBER(SEARCH(B1,$A$1))

      You can learn more about SEARCH function in Excel in this article on our blog.

  3. I'm trying to work on a formula to look up the start date and rank of an employee in table 1 and then look up table 2 and if their start date is less than 12 months return a % based on their rank but if their start date is more than 12 months ago return a different % based on their rank.

    Table 1 has start date, rack and % as column headers
    Table 2 Has Ranking A B C D as headers, Rows are 'Before 9/02/2021' and 'after 09/02/2021'. The columns are filled in with various percentages in both rows.

    I would like to look up the start date and rank in table 1 then look up table 2 and if the date on table 1 is 'before 9/02/2021' return the percentage from table 2 that matches the rank in the row 'before 09/02/2021' . If the date on table 1 is after 09/02/2021 return the percentage from table 2 that matches the rank in the row 'after 09/02/2021'

    The date in the rows on table 2 are changing on a daily basis. As table 2 rows refer to either in the last 12 months or prior to 12 months.

    I've tried a vlookup and match formulas but can't seem to get it to work. Any suggestions or better way to do this?

  4. Can I use this across the wordsheet?
    I mean the database in one sheet, and the result in other sheets.

    I try but it did not work.

  5. I have sets of data in 3 column and I need both values on column B & C to appear as an aswer when the answer is transposed:

    APPLE ABC 123
    APPLE BCD 231
    APPLE CDE 321

    ideally the answer should be:

    APPLE ABC 123 BCD 231 CDE 321

    is this possible so far my formula is this but I am only getting answer as: APPLE ABC BCD CDE

    =INDEX($B$2:$B$3650,SMALL(IF($F2=$A$2:$A$3650,ROW($A$2:$A$3650)-ROW($A$2)+1),COLUMN(A1)))

    Thank you

    1. Hello!
      To join multiple values into a string of text, use the TEXTJOIN function

      =TEXTJOIN(" ",TRUE,IFERROR(INDEX($B$2:$C$13, SMALL(IF(F$2=$A$2:$A$13, ROW($A$2:$A$13)-1,""), ROW($A$2:$A$13)-2),{1,2}),""))

      The formula works in any cell of 3 rows.

      1. Hi Alexander,

        Thank you for looking into my query. Is it possible to get Location and Quantity below to appear in separate columns? With the original formula I could only extract location horizontally but I needed to find a way that the formula would return 2 values each time it finds unique reference is in column A and the answers I would like to get are in columns B and C and returning every instance horizontally in separate columns each time. Is this possible?

        Produce Location Quantity
        APPLE BOX123 50
        ORANGE BOX124 70
        APPLE BOX125 50
        LEMON BOX125 60
        ORANGE BOX123 60
        APPLE BOX124 50

        Answer for example:

        Produce Location Quantity Location Quantity Location Quantity
        APPLE BOX123 50 BOX125 50 BOX124 50
        ORANGE BOX124 70 BOX123 60
        LEMON BOX125 60

        Thank you very much.

  6. Have you You Tube Channel?

  7. I have a list of sales interactions by client, with several interaction dates on separate rows for each client name. I've successfully used Formula 2 to populate the interaction dates in a row from newest to oldest for each client. However, I would like to add rows to my interaction list for new interactions as they occur. This expands the range beyond what is currently in the formula (for instance, if my range is $D$2:$D$100, and I add a date for a new interaction into cell D101, it doesn't get captured). Is there a way to make the range of cells automatically update to include the newly added rows? Thanks!

  8. I tried this using exactly the data in the example in Excel for Mac 16.51. It does not work correctly. In the test IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"") the formula ROW($A$3:$A$13) produces the same result regardless of the value of D2.

    Maybe this is another instance of Excel for Mac and Excel for Windows being divergent.

    1. Hello

      I have the same issue with a regular PC version, MS Office Professional Plus 2016.

      Any suggestion?

      1. Hello!

        I cannot say anything about Excel for Mac - did not have a chance to test the formulas there.

        In my Excel 365, all the formulas work fine, exactly as described in this tutorial.

        Tibor, you can downlaod our sample worsheet to check the behavior in your Excel.

  9. This is almost exactly what I have been looking for. Is there a way to modify these formulas to work with a horizontal array instead of a vertical one?

    Thanks

      1. Thanks, I'll give those a try

    2. I should add that this is the formula I would like to modify but have not been successful so far. =IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:
      $B$13)-2,""), ROW()-2)),"")

      Thanks

  10. Is this applied for Excel 2007?
    I applied the formula, only the first data came out, not multiple data.
    Thank you.

    1. Hi Kaede,

      Did you press Ctrl + Shift + Enter to complete the formula? In Excel 2007 - 2019, it only works as an array formula.

  11. Hi, Thanks for helping and passing on your knowledge. Based on the example given on the page it relates to a problem I am trying to resolve in Excel. Could you explain the calculation that would create a list of all the sellers that had sold Bananas as an example, many thanks for any assistance.

    1. Hi Chris,

      The formula is very similar to the one discussed in the first example. You just swap the lookup and return ranges:

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

      Where:

      D$2 - lookup value (bananas)
      $A$3:$A$13 - return range (seller names)
      $B$3:$B$13 - lookup range (products)

  12. Hi, how can I get/find the number in column B from the given set of numbers in column A .
    Below is the small example of the numbers. Thank you.
    A B
    223 301
    224 304
    304 307
    310 310
    311 320
    312
    317
    318
    320
    321

      1. this formula doesn't work. it show nothing. please help me. below is my data.

        DATA
        Date Time
        01/06/2021 8:01:39
        01/06/2021 12:30:42
        01/06/2021 13:29:47
        01/06/2021 22:00:49
        02/06/2021 8:00:24
        02/06/2021 12:31:21
        02/06/2021 13:29:59
        02/06/2021 19:30:22
        03/06/2021 8:01:40
        03/06/2021 12:30:07
        03/06/2021 13:30:52
        03/06/2021 19:30:24
        04/06/2021 7:56:08
        04/06/2021 12:30:04
        04/06/2021 13:30:13
        04/06/2021 19:30:25
        08/06/2021 7:54:51
        08/06/2021 13:28:26
        08/06/2021 19:30:12
        09/06/2021 7:52:59
        09/06/2021 12:34:43
        09/06/2021 13:26:36
        09/06/2021 17:28:22
        09/06/2021 20:00:38
        11/06/2021 7:58:38
        11/06/2021 13:28:32
        11/06/2021 19:30:41
        12/06/2021 7:55:55
        12/06/2021 12:31:20
        12/06/2021 13:28:27
        12/06/2021 19:30:38

        OUTPUT
        DATE TIME1 TIME2 TIME3 TIME4 TIME5 TIME6
        01/06/2021 8:30:55 12:32:18 13:53:43 21:31:36
        02/06/2021 7:30:53 12:31:48 13:42:48 13:17:01 19:31:50 20:32:07
        03/06/2021 7:31:51 12:30:27 13:40:55 20:32:33
        04/06/2021 7:30:04 12:31:22 13:23:16 20:30:25
        05/06/2021
        06/06/2021
        07/06/2021
        08/06/2021 7:22:48 12:43:30 13:28:04 20:30:55
        09/06/2021 7:43:20 12:30:02 13:40:55 20:31:15
        10/06/2021 7:37:03 12:33:04 14:33:19 20:30:21
        11/06/2021 7:41:30 12:35:39 14:13:33 20:31:19
        12/06/2021 7:30:08 12:31:05 13:15:40 20:01:44

  13. Hi, I am trying to do a formula like this in a spreadsheet where I utilize multiple tabs to look up some info and yield multiple results in consecutive rows that fir the criteria. The primary tab that these lookup formulas would be in is called the Vacation Chart tab. in cell A8 of that tab would be a dropdown menu consisting of departments. Cell B8 would also be a dropdown menu conisting of shift codes. There are 3 columns I would be working with on this tab that I would want to yield multiple info from the lookups to other tabs: A11:A42 would list every employee number that matches the criteria in A8 and B8. B11:B42 would list names.

    The employee numbers can be found in a tab called New bid in A5 and onward, Names can be found in B5 and onward in the same tab. Additionally, The Dept codes assigned to those employees are contained in G5 and onward and the shift codes are in I5 and onward.

    So I want to be able to find for example a list of all the employees who are in a dept code called C61C with a shift code of 3.

    How could I do this?

    1. Hello!
      The examples in this article explain how to VLOOKUP multiple values with one or more criteria. Please check them out.

  14. how can i add AND formula inside this
    IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range , ROW(lookup_range) - MIN(ROW(lookup_range ))+1,""), ROW() - n)),"")
    like example
    C1 | F | D
    A1| B | C
    A2 | D | D
    B1 | E | C
    A3 | B |E
    A4 | B | D

    IFERROR(INDEX(A1:A6, SMALL(IF(AND(B = B1:B6,D= C1:C6), ROW(A1:A6)- MIN(ROW(A1:A6 ))+1,""), ROW() - n)),"")
    the result will show the A1| B | C
    that D = C1:C6 will take the data from the 1st line, not same line with B=B1:B6
    how can i solve it?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      {=IFERROR(INDEX($A$1:$A$6,SMALL(IF(IF(($B$1:$B$6="B")*($C$1:$C$6="D")=1,TRUE),ROW($A$1:$A$6)- MIN(ROW($A$1:$A$6))+1,""), ROW()-1)),"")}

      This is the formula for cell E2.
      The AND and OR operators do not work with arrays.

  15. I used the formulas mentioned on a second worksheet (to pull data from the first). The results step down a row with each new column. I didn't see anyone else with the same problem in the comments. Did I miss a step? I would like to send you my spreadsheet or screen share to show you the problem.

    1. I'm having the same issue the results step down a row.

      Did you figured out the issue?

      P.S. I like so much your very detailed posts for most issues i ever had

    2. Also, if i increase n by 1 for each new column it produces the correct result. Trying to understand why this works and find a solution to prevent having to change this value in multiple cells.

  16. Hi,
    Thanks for the formula, it has been really useful! I have a slightly complicated question related to it:

    I have an class attendance spreadsheet that is linked to another master workbook (of all students in the university). I have used formula 2 from above to get the names based on their class and year. So far the formula works great and does what I need it to.

    I then have an area where I mark down Y/N depending on the students' attendance. This works well too. My issue arises when a new student is added to the master spreadsheet. This addition is alphabetical. The formula therefore places the new student alphabetically in the list in my class attendance sheet. This means that if I have already marked a student present, but a new student joins the class late, the attendance will not necessarily reflect the correct student.

    Is there a way to ensure the students attendance stays linked to the students name?

    If needed I can send through my sheets to make it clearer!

    Thanks!

    1. Hello!
      I don't know which of the many formulas above you used. But to solve the problem of adding new students, I recommend converting your original data to an Excel table or using a dynamic named range.
      If you describe in detail the initial data, the desired result and your formula, I will try to give you a more accurate advice.

  17. Once entered the value in a cell in Excel , how to protect that particular row i.e) for ex once entered the value in a cell F4, how to protect that the row No.4 or protect A4 : F4

  18. Hi,
    I am using Formula 2, and it is great!!!
    How would I be able to search multiple worksheets for D3?
    Thank you for the wonderful work that you do!
    George

  19. Good day,
    I am facing difficulty on the following: I have table with sales values and customer names (one column each). On the sales column, there are values that are duplicate, and they belong to different customers. I am trying to pinpoint the top 10 of those and lookup the top 10 customers, but i get the same customer name, when it comes to duplicate values.
    Can you please assist me on this matter?

    Thank you

    1. One simple way :

      1. In the Costumer column apply Advanced Filter,check "Copy to another Location", check "Unique records Only".

      2. In a column next to the new Unique Customer column, put a column with a heading like Sum_of_sales,in the cells below, the formula: =SUMIF(costumer_range,J2,sales_range). Drag to the rest of Sum_of_sales column.

      3. Apply Autofilter to the resulting range(consisting of the 2 new columns), then filter the sum_of_sales column,sorting it in descending order.

  20. This is a solution involving vba.

    1. Name the range where the countries, and only the countries, are located as "Countries"( as global name). Do not include headings.

    2. Press ALT+F11 to open the VBA editor

    3. In the menu choose Insert ->Module

    4. Paste this code in the resultant window

    Public Function CollabCountries(strCountry As String) As String
    Dim rCountries As Range, cell As Range, dCollab As Object, _
    tmp As String, rRow As Range, IsInRow As Boolean, k, _
    result As String
    Set rCountries = ThisWorkbook.Names("Countries").RefersToRange
    Set dCollab = CreateObject("scripting.dictionary")
    For Each rRow In rCountries.Rows
    IsInRow = False
    For Each cell In rRow.Cells
    If cell.Value = strCountry Then IsInRow = True: Exit For
    Next cell
    If IsInRow Then
    For Each cell In rRow.Cells
    If cell.Value strCountry And _
    Len(cell.Value) > 0 Then _
    dCollab(cell.Value) = dCollab(cell.Value) + 1
    Next cell
    End If
    Next rRow
    For Each k In dCollab.keys
    If dCollab(k) > 0 Then
    result = result & k & "(" & dCollab(k) & ")"
    End If
    Next k
    CollabCountries = result
    End Function

    5. Next to each cell in a range containing th countries names , put the formula CollabCountries(cellAdress). For instance, if the the name USA is in cell B9,cell C9 will contain CollabCountries(B9), showing the countries collaborating with USA in common proyects. Then drag to the rest of countries.

    1. That was in response to Linda´s comment(# 56),above
      I did a mistake locating my message

      Forgot to say:
      6. Save the excel workbook with the .xslm extension

      The above code is not showing correctly in the line where appears the following:
      If cell.Value strCountry And _
      Len(cell.Value) > 0 Then _

      it should be:
      If Not (cell.Value = strCountry) And _
      Len(cell.Value) > 0 Then _

      I guess the message viewer has some issues with the vba not equal operator

      1. Pardon me, is xlsm extension

  21. Sorry, sent before finishing, the full question is:
    I feel like I could use something similar for my dataset, but cannot figure out how to adopt it. My data are like this:
    Coutry1 Country2 Country3 Country4
    Italy France USA
    Germany
    USA Singapore China Nigeria
    USA France
    Each row is a project - some of them are one-country and some are international collaborations with differing number of countries. I would need to find and count which other countries each country has collaborated with (and ideally also how many times).

    In this example the desired end output would be:
    USA: Italy(1)France(2)Singapore(1) China(1) Nigeria(1)
    France: Italy(1), USA(2) and so on.

    Any help would be appreciated!

    1. make copy of heading "contry 1,2,3,4" on same sheet or another or other excel then use =countif(country 1 full column, first row enter

  22. Thanks for your reply Alex!
    I am using your first example "Formula 1".
    =IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")

    I'll use your example except rather than seller names and product names,
    its all percentages. And I have 5 columns with different percentages.
    example:

    LOOKUP TABLE
    A B C D E
    9% | 10% | 4% | 2% | 36%
    9% | 10% | 4% | 2% | 36%
    9% | 12% | 4% | 2% | 36%
    9% | 12% | 4% | 2% | 36%
    9% | 10% | 4% | 5% | 36%
    9% | 10% | 4% | 5% | 36%
    9% | 10% | 4% | 5% | 36%
    9% | 10% | 4% | 6% | 36%
    9% | 10% | 4% | 6% | 36%

    Let's say I need to know the most common "%" values WITH the 9% from column A
    in the D Column. The most common to the least common.

    RESULT
    2% |
    5% |
    6% |

    The only problem is I have more than 2000 rows

    I am using a formula to get the most common values in column A
    =MODE(IF(1-ISNUMBER(MATCH($A$2:$A$2000,$K$1:K2,0)),$A$2:$A$2000))

    But from there, I have no idea how to extract the most common to least common
    values in column D with the 9% value in Column A

    I hope this is clear enough for you? :/

    Thanks for the help Alex!

    1. Thank you for the clarification, Luc.

      Please select the vertical range of 5-10 cells so that there are all the repeating values there. Then enter the following formula right in the formula bar:

      =MODE.MULT((D1:D2000) * ((A1:A2000)=0.09))

      Since this is an array formula, hit CTRL+SHIFT+ENTER to apply it. You'll see all the most common values with 9% in column D. The rest of the cells will be filled with N/A.

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Hi Alex!
        I've extracted only the 9% so it would be easier to sort the D column.
        I tried your formula and strangely its giving me the most common value(4%) but denying the rest. Lets say the 0% would be the 2nd but instead its 4% for all of them.
        I have noticed when the 4% and the 0% had the same amount of occurrence, the result was 1st(4%) 2nd(0%) and #N/A for the rest.
        Not sure whats going on. Do you have any idea what I'm missing??

        Thanks for the formula and your help!

  23. Hi! I was wondering if we could add the MODE function to this formula!?
    So it would place the 4 most common values.
    I just can't figure out how to add it :(

    Would this be possible?
    Thanks in advance for any help you can give me :)

    1. Hello Luc!
      For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Thank you.

      1. Hello Alex! I thought I replied with the reply button but it seems like i didn't.
        my response is the next comment! Sorry ;)

  24. Hi
    Please help me to get the formula for the below scenario.
    In sheet 1, A column have few name list like below
    vijay
    Murali
    Nandha
    karthi
    In sheet 2,A column have few names with extra letter like below
    Vijay_grp1
    britto
    Murali_kronos
    Sundar
    Karthi_abcd

    So the question is, what is Vlookup formula to find the values from sheet 1 vs sheet 2.
    I like to know the Vlookup formula to put it from sheet 1 to sheet 2 to find the same name in the sheet to. Please help me to solve it.

    1. Hello Vijay,
      You'll need th INDEX+MATCH combination for this task. Supposing that column A contains names, column with names with extra letter is column E and the lookup column is F, the formula would be:
      =INDEX($F$1:$F$5, MATCH(A1, LEFT($E$1:$E$5, LEN(A1)), 0))

      Please check out this article, I beloeve you'll find this information helpful

  25. Hello. I am trying to use this formula with wildcards on the lookup values so that partial matches are printed out. I am using the following formula:
    =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)),"")
    but don't get any matches. Please help!

    1. I am facing the same problem, any solution?

  26. =IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

    Hello, I have something very similar. Let's say you have multiple columns of Seller (Seller1, Seller2, Seller3, Seller4)(Move product to column E) Is there a way that $A$3:$A$13 can automatically change to B, C, or D based off of inputting Seller 1, 2, 3, or 4?

    1. Hi Dan,

      The only working solution I can think of is nesting or concatenating different IF functions. For example:

      =IF($H$2=$B$2, IFERROR(...), IF($H$2=$C$2, IFERROR(...), ""))

      or

      =IF($H$2=$B$2, IFERROR(...), "") & IF($H$2=$C$2, IFERROR(...), "")

      If someone has a better idea, please do post your solution here!

    2. Hello Dan,
      Thank you for your comment.

      We have just replied to you via email. Please provide us with the requested information and we’ll do our best to help. Thank you.

  27. Hi,
    I have used Formula 3 with returns in a column.
    My dashboard has 3 look up values. The formula only gave 1 result instead of 3 or more.
    Please help.
    Here's my formula:

    =IFERROR(INDEX(Listings!$E$2:$E$1000,SMALL(IF(1=((--($B$4=Listings!$A$2:$A$1000))*(--($C$4=Listings!$B$2:$B$1000))*(--($D$4=Listings!$C$2:$C$1000))),ROW(Listings!$E$2:$E$1000)-1,""),ROW()-3)),"")

    Thank you.

    1. Hi,

      This has been solved. haha.
      I only need to copy the formula to all rows in that specific column.

      Thanks

  28. Hi Svetlana,
    When I applied the same example (as a training) in a blank sheet, it didn't work for me. After I read the comments I download the example file, I made a double click on a cell contains the formula to check it, I say ok, then press enter , as surprise I found the word displayed disappear, I don't know really why this is happening. I tried Online Excel to check maybe the excel version I had, has an issue or something.
    Please Help
    Thanks,

    1. I forgot to say that a note showed up to me when I activate it , it said "inconsistent formula"

      1. Ok the problem solved (CTRL+SHIFT+ENTER not just enter)
        thanks

  29. =IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")
    can anybody modify this so it does a partial text search

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

  30. I stumbled upon this formula and spent about 2 hours trying to get it to work, but I finally did after seeing an error I was making in the comments (our fault for not reading fully)

    My question is, using the very top example with sellers and produce, I manually need to change my cell reference from $D2 to $D3. My spread sheet contains over two hundred sellers and about a hundred different types of produce. So in a separate column I have my sellers, Adam being D2 and Robert D3. I can drag the formula for Adam all 100 rows.

    But if I stop at row 25, and switch to D3 for Robert, I cannot copy them down the same way. I have to modify $D3 and ALSO the row to be -24 (when it was originally -2).

    Is there a way around this? I'd rather only have to change one aspect instead of two if possible because there are so many. I can't set a predetermined spacing because it changes daily.

    Thanks!

    1. Side note, the formula I was using was this one below:

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

  31. HI
    When i use this code the excel have a error
    =IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")
    the core of error begins from==>
    IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"")
    i dont know what should i do :-(

    1. thank you
      i fund the issue
      the problem was in
      ROW()
      cause of copy pasting the formula it reffer to the cell which is copied and it is not the rank that we want. so should change to 1,2,3 or "ROW()-cell row number+1"

  32. This was a great tutorial, thank you very much! I got the formulas to work for my database, however I have an additional restriction. Bringing it to this example (I'm doing results in a ROW), let's say the products are listed as ORANGES - APRICOTS - BANANAS - APPLES - LEMONS. So if Adam sold Bananas, Oranges and Lemons, I want his results to show ORANGES - *blank* - BANANAS - *blank* - LEMONS. To make it easier to understand, my categories are time, so I need the results chronologically (6-10-12-14 hours). So if a result appears in 12 and 14, I want to see the 4 columns as blank-blank-result-result, but what I'm getting is result-result-blank-blank. Any thoughts?

    1. Hi Francisco,
      I guess you get precisely that result your are looking for by just stripping the SMALL function as follows bellow. It seems that the SMALL function is being used to fill up the blank cells so that the looked up products at the end they come one after another.

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

      I hope my reasoning sounds correct.

  33. Hello - I tried to implement Formula 1 in this article, Vlookup multiple values and return results in a column, using the same data entered in the same cells in an Excel 2019 spreadsheet. But the formula doesn't work. To see why, I stripped out the IFERROR code and just ran the INDEX function without error checking. This showed that the formula produces a #Value error in the result. Does anyone know why the example is not working for me? I'm truly stumped.

    1. Hi Larry,
      It's difficult to say what the problem might be without seeing your data and formula. IFERROR is a very simple function and usually it does not cause any issues.

      You can download our sample workbook with working formulas and copy/past the desired formula from there to try it on your data.

      1. I have a similar issue, happening with your sample workbook too. I open the sample workbook, all the formulas show exactly as on this site. If I then click in the formula bar and hit enter the calculated values that were there when I open the book disappear. The formula is still there it just returns the error result value. The only difference in the formula bar is that the {} brackets round the formula are no longer there after calculating the formula locally.

        1. James,
          You most likely need to re-apply the "Ctrl-Shift-Enter' command to let the formula know it is based on an ARRAY.
          Put your cursor in the cell with the formula and press Control-Shift and Enter.
          Just a thought...

  34. I need help to get Max value from set value but if any data is 0 ( zero) then return result should be 0(zero). Pls see below data & result what I want. Pls advise.
    A 20
    B 20
    C 30
    A 40
    B 10
    D 60
    B 35
    D 40
    A 0
    D 0
    A 40

    Result should be
    A 0
    B 35
    C 30
    D 0

    1. This should do it. Assuming your data begins in cell A1. Make sure you hit Ctrl+Shift+Enter when finished as this is an array formula. {=IF(MIN(IF("A"=$A$1:$A$11,$B$1:$B$11,""))=0,0,MAX(IF("A"=$A$1:$A$11,$B$1:$B$11,"")))}

  35. I need help to make a formula to retrieve information from a 'master' sheet (where information is entered manually) and populate specific cells in a 'target' sheet to avoid re-entering this information.

    For example,

    Master sheet
    -----------

    A B C D
    1 Device ID Device MAC Hostname Service Tag
    2 FS-001 00:00:00:00:00:00 device01 12345

    Target Sheet
    ----------

    A B C D E
    1 Device ID Hostname IP address Device MAC Gateway
    2 FS-001 device01 00:00:00:00:00:00

    So, if on the 'Target' sheet A2 = 'Master' sheet A2's value of FS-001, then values from Master sheet B2 and C2 will be populated
    in the cells of 'Target' sheet D2 and B2 respectively.

    1. Have you found the formula for this? I need a similar one too.

  36. Good day !
    Using the row array formula =IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), ""), let's say that Adam's first line column B is blank rather than "Bananas" - how can I skip the blank and show the next real value in cell E1? (in other words, E1 would be "Oranges")
    Thanks!

    1. =IFERROR(INDEX($B$3:$B$13,SMALL(IF(($D3=$A$3:$A$13)*($B$3:$B$13""),ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")

      1. =IFERROR(INDEX($B$3:$B$13,SMALL(IF(($D3=$A$3:$A$13)*($B$3:$B$13 <>""),ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")

    2. Did you find the answer to this question?

  37. Hi svetlana,

    Appreciate if you can help me in this situation for my survey data. I am doing this to calculate for Fuzzy Delphi method

    I have:
    survey data ---> (A)
    Likert scale 1-7 ---> (B)
    Decimal point scale in 3 columns ---> (C)
    Empty cell putting (C) values after comparing (A) and (B)---> (D)

    Example as below:

    (C) (B)
    ------------------------------------------
    decimal point scale likert scale
    0.0 0.0 0.1 7
    0.0 0.1 0.1 6
    ------------------------------------------

    I have a list of survey data (A) and I want to compare with likert scale (B), if (A) is equal or more than (B), then I want to take all values in (C) and copy it in (D)

    What function should be suitable to get the return values?

    Thank you

    1. (C)
      decimal point scale
      0.0 0.0 0.1
      0.0 0.1 0.1

      (B)
      likert scale
      7
      6

  38. Светлана, спасибо вам огромное за ваш форум. Начал составку очень трудного Excel spreadsheet сегодня, по знанию начиная почти с нуля по формулам. Последняя черта документа оставалась включить одну из этих формул упомянутых здесь. Туго было понимать но в конце концов понял где ошибка была, поправил и всё заработало! Как приятно что есть такие умные люди которые напишут как что делать ^^

    1. Спасибо на добром слове, Егор! Приятно знать, что соотечественники нас тоже читают :)

  39. hello

    Svetlana Cheusheva

    Sorry to say but your formula does't work. they have some problem while applying in practical.

    so can you help me ?

    1. Hello!
      Please feel free to download our sample workbook with all the formulas described in this tutorial, and try them on your data.

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

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

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

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

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

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

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

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

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

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

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

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