How to Vlookup multiple values in Excel with criteria

The tutorial shows a few ways to Vlookup multiple matches in Excel based on one or more conditions and return multiple results in a column, row or single cell.

When using Microsoft Excel for data analysis, you may often find yourself in situations when you need to get all matching values for 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.

Vlookup for multiple values can be done via a combined use of several functions. If you are far from being an Excel expert, don't hurry to leave this page. I will do my best to explain the underlying logic so that even a novice could understand the formulas and adjust them for solving similar tasks. Even better, I will show you another possible solution that takes just a few mouse clicks and does not require any knowledge of Excel formulas at all!

How to do multiple Vlookup in Excel using a formula

As mentioned in the beginning of this tutorial, there is no way to make Excel VLOOKUP return multiple values. The task can be accomplished by using the following functions in an array formula:

  • IF - evaluates the condition and returns one value if the condition is met, and another value if the condition is not met.
  • SMALL - gets the k-th smallest value in the array.
  • INDEX - returns an array element based on the row and column numbers you specify.
  • ROW - returns the row number.
  • COLUMN - returns the column number.
  • IFERROR - traps errors.

Below you will find a few examples of such formulas.

Formula 1. Vlookup multiple matches and return results in a column

Let's say, you have the seller names in column A and the products they sold in column B, column A containing a few occurrences of each seller. Your goal is to get a list of all products sold by a given person. To have it done, please follow these steps:

  1. Enter a list of unique names in some empty row, in the same or another worksheet. In this example, the names are input in cells D2:G2:
    The source data to Vlookup multiple matches in Excel

    Tip. To quickly get all different names in a list, you can use the UNIQUE function in Excel 365 or a more complex formula to extract distinct values in older versions.

  2. Under the first name, select a number of empty cells that is equal to or greater than the maximum number of possible matches, enter one of the following array formulas in the formula bar, and press Ctrl + Shift + Enter to complete it (in this case, you will be able to edit the formula only in the entire range where it's entered). Or, you can enter the formula in the first cell, hit Ctrl + Shift + Enter, and then drag the formula down to a few more cells (in this case, you will be able to edit the formula in each cell individually).

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

    or

    =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)),"")

    As you see, the 1st formula is a bit more compact, but the 2nd one is more universal and requires fewer modifications (we will elaborate more on the syntax and logic a bit further).

  3. Copy the formula to other columns. For this, select the range of cells where you've just entered the formula, and drag the fill handle (a small square at the lower right-hand corner of the selected range) to the right.

The result will look something similar to this:
A formula to Vlookup multiple values and return results in a column

How this formula works

This is an example of intermediate to advanced uses of Excel that implies basic knowledge of array formulas and Excel functions. Working from the inside out, here's what you do:

  1. IF function

    At the core of the formula, you use the IF function to get the positions of all occurrences of the lookup value in the lookup range: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"")

    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.

    The relative positions of the rows are calculated by subtracting 2 from ROW($B$3:$B$13) so that the first row has position 1. If your return range begins in row 2, then subtract 1, and so on. The result of this operation is the array {1;2;3;4;5;6;7;8;9;10;11}, which goes to the value_if_true argument of the IF function.

    Instead of the above calculation, you can use this expression: ROW(lookup_column)- MIN(ROW(lookup_column))+1, which returns the same result but does not require any changes regardless of the return column location. In this example, it'd be ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1.

    So, at this point you have an array consisting of numbers (positions of matches) and empty strings (non-matches). For cell D3 in this example, we have the following array:
    An array with the positions of matches and empty strings for non-matches

    If you check with the source data, you will see that "Adam" (lookup value in D2) appears on the 3rd, 8th and 10th positions in the lookup range (A3:A13).

  2. SMALL function
    Next, the SMALL(array, k) function steps in to determine which of the matches should be returned in a specific cell.

    With array already established, let's work out the k argument, i.e. the k-th smallest value to be returned. For this, you make a sort of an "incremental counter" ROW()-n, where "n" is the row number of the first formula cell minus 1. In this example, we entered the formula in cells D3:D7, so ROW()-2 returns "1" for cell D3 (row 3 minus 2), "2" for cell D4 (row 4 minus 2), etc.

    As the result, the SMALL function pulls the 1st smallest element of the array in cell D3, the 2nd smallest element in cell D4, and so on. And this transforms the initial long and complex formula into a very simple one, like this:
    The position of the matching value to be returned in a given cell

    Tip. To see the calculated value behind a certain part of the formula, select that part in the formula bar and press F9.

  3. INDEX function

    This part is easy. You use the INDEX function to return the value of an array element based on its row number.

  4. IFERROR function

    And finally, you wrap the formula in the IFERROR function to handle possible errors, which are inevitable because you cannot know how many matches will be returned for this or that lookup value, and therefore you copy the formula to a number of cells equal to or greater than the number of possible matches. Not to scare your users with a bundle of errors, simply replace them with an empty string (blank cell).

Note. Please notice the proper use of absolute and relative cell references in the formula. All references are fixed except for the relative column reference in the lookup value (D$2), which should change based on a relative position of a column(s) where the formula is copied to return matches for other lookup values.

Putting all this together, we get the following generic formulas to Vlookup multiple values in Excel:

Formula 1:

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

Formula 2:

IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range , ROW(lookup_range) - MIN(ROW(lookup_range ))+1,""), ROW() - n)),"")

Where:

  • m is the row number of the first cell in the return range minus 1.
  • n is the row number of the first formula cell minus 1.

Note. In the above example, both n and m are equal to "2" because our return range and formula range both begin in row 3. In your worksheets, these may be different numbers.

Formula 2. Vlookup multiple matches and return results in a row

In case you want to return multiple values in rows rather than columns, change the above formulas this way:

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

Or

=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)), "")

Like in the previous example, both are array formulas, so remember to press the Ctrl + Shift + Enter shortcut to complete them correctly.
Formula to Vlookup multiple matches and return results in rows

The formulas work with the same logic as in the previous example, except that you use the COLUM function instead of ROW to determine which matching value should be returned in a specific cell: COLUMN()-n. Where n is the column number of the first cell where the formula is entered minus 1. In this example, the formula is input in cells E2:H2. With E being the 5th column, n is equal to "4" (5-1=4).

Note. For the formula to get copied correctly to other rows, mind the lookup value references, absolute column and relative row, like $D3.

Wrapping up, here are the generic formulas for Vlookup with multiple results returned in rows:

Formula 1:

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

Formula 2:

IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(lookup_range) - MIN(ROW( lookup_range))+1,""),COLUMN() - n)), "")

Where:

  • m is the row number of the first cell in the return range minus 1.
  • n is the column number of the first formula cell minus 1.

Formula 3. Vlookup multiple matches based on multiple conditions

You already know how to Vlookup for multiple values in Excel based on one condition. But what if you want to return multiple matches based on two or more criteria? Taking the previous examples further, what if you have an additional Month column, and you are looking to get a list of all products sold by a given seller in a specific month?

If you are familiar with arrays formulas, you may remember that they allow using asterisk (*) as the AND operator. So, you can just take the formulas discussed in the two previous examples, and have them check multiple conditions as demonstrated below.

Return multiple matches in a column

IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")

Where:

  • m is the row number of the first cell in the return range minus 1.
  • n is the row number of the first formula cell minus 1.

Assuming the Seller list (lookup_range1) is in A3:A30, the Month list (lookup_range2) is in B3:B30, the seller of interest (lookup_value1) is in cell E3, and the month of interest (lookup_value2) is in cell F3, the formula takes the following shape:

=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)),"")

This layout may be useful for creating a dashboard, e.g. your users can enter a name in E3, month in F3 and get a list of products in column G:
Vlookup with multiple criteria returning multiple matches in a column

Return multiple results in a row

If you want to pull multiple values based on multiple criteria sets, you may prefer the horizontal layout where results are returned in rows. In this case, use this following generic formula:

IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")

Where:

  • m is the row number of the first cell in the return range minus 1.
  • n is the column number of the first formula cell minus 1.

For our sample dataset, the formula goes as follows:

=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E3=$A$3:$A$30)) * (--($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), COLUMN()-6)),"")

And the result can resemble this:
Vlookup with multiple criteria returning multiple matches in rows

In a similar manner, you can do multiple Vlookup with three, four or more conditions.

How these formulas work

Basically, the formulas to Vlookup multiple values with multiple conditions work with the already familiar logic, explained in the very first example. The only difference is that the IF function now tests multiple conditions:

1=((--(lookup_value1=lookup_range1))*(--(lookup_value2=lookup_range2))*…)

The result of each lookup_value=lookup_range comparison is an array of logical values TRUE (condition is met) and FALSE (condition is not met). The double unary operator (--) coerces the logical values into 1's and 0's. And because multiplying by zero always gives zero, in the resulting array, you have 1 only for those elements that meet all of the specified conditions. Now, you simply compare the final array with number 1 so that the ROW function returns the numbers of rows that meet all the conditions, an empty string otherwise.

A word of caution. All of the multiple Vlookup formulas discussed in this tutorial are array formulas. As such, each formula iterates through all elements of the arrays every time the source data is changed or the worksheet is recalculated. On large worksheets containing hundreds or thousands of rows, this may significantly slow down your Excel.

If you need to get matches from several sheets, use this guide: How to VLOOKUP across multiple sheets.

How to Vlookup to return multiple values in one cell

I will be upfront - I don't know an easy way to lookup and return multiple matches in a single sell with formulas. However, I do know a formula-free (read "stress-free" :) way to do this by using two add-ins included with our Ultimate Suite for Excel. The detailed steps follow below.

Source data and expected result

As shown in the screenshot, we continue working with the dataset we've used in the previous example. But this time we want to achieve something different - instead of extracting multiple matches in separate cells, we want them to appear in a single sell, separated with a comma, space, or some other delimiter of your choosing.
Source data and expected result

Pull rows with multiple matches to the main table

In your main table, enter a list of unique names in the first column, months in the second column, and arrange them like shown in the screenshot below. After that, carry out the following steps:

  1. Select your main table or click any cell within it, and then click the Merge Two Tables button on the ribbon:
    Merge Two Tables button on the Excel ribbon
  2. The add-in is smart enough to identify and pick the entire table, so you just click Next:
    Select the main table.

    Tip. When using the tool for the first time, it stands to reason to select the Create a backup copy of the worksheet box in case something goes wrong.

  3. Select the lookup table, and click Next.
    Select the lookup table.
  4. Choose one or more matching pairs of columns that should be compared in the main table and lookup table (in this example, it's the Seller and Month columns), and then click Next.
    Choose one or more matching pairs of columns.
  5. Select the column(s) from which you want to pull matching values (Product in this example), and click Next.
    Select the column(s) from which you want to pull matching values.
  6. Tell the add-in how exactly you want multiple matches to be arranged in the main table. For this example, we need the following option: Insert rows with duplicate matching values after the row with the same value. Make sure that no other option is selected and click Finish.
    Specify how to arrange matching values.

At this point, you will have the following result - all matching rows are pulled to the main table and grouped by the values in the lookup columns - first by Seller, and then by Month:
Matching rows are pulled to the main table.

The resulting table already looks nice, but it's not exactly what we wanted, right? As you remember, we are looking to Vlookup multiple matches and have them returned in a single sell, comma or otherwise separated.

Combine duplicates rows into one row

To merge "duplicate rows" in a single row, we are going to use another tool - Combine Rows Wizard.

  1. Select the table produced by the Merge Tables tool (please see the screenshot above) or any cell within the table, and click the Combine Rows button on the ribbon:
    Combine Rows button on the ribbon
  2. Check if the add-in's got the table right, and click Next:
    Select the table.
  3. Select the key column or columns (Seller and Month in this example), and click Next:
    Select the key column(s).
  4. Select the column(s) that contains multiple matches (Product in this example), choose the desired delimiter (semicolon, comma, space or line break), and click Finish.

    Optionally, you can enable one of the additional features, or both:

    • Delete duplicate values - if the column to be merged contains a few identical values, the first occurrence will be kept, duplicate matches will be deleted.

      In this example, we do not check this option, and the add-in returns all found matches. For example, in cell C2, we have this string: Lemons, Bananas, Apples, Lemons, Bananas (please see the result on step 5 below). If you choose to delete duplicates, the result would be: Lemons, Bananas, Apples.

    • Skip empty cells - self-explanatory :)
      Select the column(s) from which you want to pull multiple values and choose the desired delimiter.
  5. Allow the add-in a few seconds for processing, and you are all set!
    Multiple matches are returned in a single sell, comma separated.

This is how you can look up and return multiple values in Excel using our tools. If you are curious to give them a try, a fully-functional evaluation version of the Ultimate Suite is available for download below.

Available downloads

Vlookup Multiple Values - examples (.xlsx file)
Ultimate Suite - trial version (.exe file)

Other ways to Vlookup in Excel

342 comments

  1. Page No ID Description unit Qty
    S2/1/3 A Gypsum ceiling m2 ----
    S2/1/3 D Bulkhead including paint finish, 150 mm high m -----
    S2/1/3 E Bulkhead including paint finish, 150 mm high m -----
    S2/1/3 F Bulkhead including paint finish, 200 mm high m -----I

    I need formula for above blank
    from following tabel. Table name is MasterBoQ.
    Page No ID Description Qty
    S2/1/2 C Veneer solid WD-06 56
    S2/1/2 Metal trim
    S2/1/2 D Golden bronze patina MT-01 9
    S2/1/2 Glass panel including fixing, backing, glue, accessories,fittings, trims, etc. complete. The Contractor will allow for laying in pattern and combination of finishes where applicable
    S2/1/2 E Clear glass GL-01 2
    S2/1/2 F Fluted glass GL-03 7
    S2/1/3 BILL NO. 1 - ONE BED SUITE, (CONT'D.)
    S2/1/3 FINISHES, (Cont'd.)
    S2/1/3 Ceilings
    S2/1/3 Drg Ref: DD-ID-63-03 and Detail Drgs.Suspended ceilings including suspension system, cornices, recesses, accessories, recessed light areas, shadow gaps, fittings, paintings, special features, access panels, etc.complete
    S2/1/3 A Gypsum ceiling - 99
    S2/1/3 B Paint vinyl emulsion PT-01 79
    S2/1/3 C Paint vinyl emulsion PT-02 20

  2. Hi Svetlana,
    Thank you for these wonderful Excel tools and techniques!
    I am implementing Formula #2 from "Vlookup with multiple matches returned in rows". Since my data is in different rows and columns from your example, I had to adjust all my values. The only other significant difference was that my Lookup values are located on another Worksheet. The good news is I was able to get the formula to return a value in the first cell. Yea!
    The bad news is when I copy down or across, I can't get any other cells to work. I triple checked my relative and absolute references to ensure they are correct and I have made sure that Ctrl-Shift-Enter has been applied to each and every cell containing the formulas. Here is an example of the formula that is working in the first cell:
    =IFERROR(INDEX('EMPLOYEE CERTS'!$B$2:$B$12220, SMALL(IF($H2='EMPLOYEE CERTS'!$A$2:$A$12220, ROW('EMPLOYEE CERTS'!$A$2:$A$12220)-MIN(ROW('EMPLOYEE CERTS'!$A$2:$A$12220))+1, ""), COLUMN()-8)), "")
    The Lookup data is found on a Worksheet named "EMPLOYEE CERTS"
    Column H contains my lookup value (notice only the Column is Absolute, the Row is relative)
    Do you see anything that looks incorrect?
    Can I send you a file which will make this alot easier for you to see what's going on?
    Thank you for your help,
    Dave

    • Hi Dave,

      Yes, please send a sample worksheet to our support address (support@ablebits.com) att: Svetlana. At first sight, the formula looks correct. I believe working with the source data directly will help to figure out the issue.

  3. Salute to whoever created the Combine Rows feature. 8195 rows have been merged into 96 rows. Thanks!

  4. OP, I need some explanation regarding your formula ,[IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range , ROW(lookup_range) - MIN(ROW(lookup_range ))+1,""), ROW() - n)),"")].
    So, i copied your formula and succeeded. But my main problem is the formula start capture value from 5th row.
    For example, i have value in cell A1:A30. The formula capture the value starting from A5:A30.
    FYI, the value i captured is from another sheet.
    Thank you OP

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

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

    • 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!

  6. Hi,
    I need something similar to the above but I have been going round in circles to find the right formula to use.
    I have text in column A2 and I have a list of key words that I'm trying to find in column A2. I'd like the formula to return all of those key words that are contained in column A2. They don't have to be all in the same cell but I just need all of the values returned. Any help would be appreciated!
    THanks

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

    • Hi,

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

      Thanks

  8. need to vlookup in accordance with date association. Any idea? Trying to put a week or months worth of data together and write a code to return value associated with a number on each date.

  9. What if the results are to return as a , (or ;) separated list in 1 cell?

  10. When I replicate this example sometimes it works, sometimes its blank, and I cant figure out why. Any ideas on why the result sometimes comes out blank? No errors or anything just blank.
    I'm doing this formula (the second one way up at the top)
    =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)),"")

  11. Sl.No ROLL NUMBER Student Name Application Id Course /Course Year Rtf Amount
    1 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
    2 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
    3 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
    4 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
    5 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
    6 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
    7 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
    8 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
    9 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 7450
    10 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 3725
    11 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 3725
    12 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
    13 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
    14 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
    15 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
    16 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
    17 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
    18 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
    19 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
    20 16G01A0401 A CHANDANA 201709098907 BTECH (ECE)/2 8750
    21 16G01A0401 A CHANDANA 201709098907 BTECH (ECE)/2 26250
    22 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725
    23 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725
    24 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725
    25 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725

    I prepared this data based on the v look up fromula .now i need name wise total in from four coloumns in one cell in a separate row. To know the individual value. can you help for this

  12. Thank you so much for this! This is exactly the help I needed. Also, I made a change to Formula 2 to make it a little more generic. Changed 'n' to row(first_formula_cell - 1).

  13. 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,

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

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

  14. Res ID Date Feedback Res ID Date
    18354576 7/17/2019 All good 18354576 7/17/2019
    19165239 7/30/2019 All good 19165239 7/30/2019
    18354576 7/31/2019 More Than 5 Error 18975236 7/31/2019
    19167323 7/31/2019 All good 19167323 7/31/2019
    18354576 7/31/2019 Less than 5 Errors 18785226 7/31/2019
    19161337 7/31/2019 All good 19161337 7/31/2019
    19153708 7/30/2019 Less than 5 Errors 19153708 7/30/2019
    19162077 7/31/2019 All good 19162077 7/31/2019
    19164149 7/31/2019 All good 19164149 7/31/2019
    19164647 7/31/2019 Less than 5 Errors 19164647 7/31/2019
    19166599 7/31/2019 All good 19166599 7/31/2019
    19161016 7/31/2019 All good 19161016 7/31/2019

    Hi
    There is multiple entries in res ID and I want vlookup data same as it is in sheet, I mean res id 18354576 having 3 or more entries with different comment and different date.
    How can I find exact comment with particular date for all entries.

  15. =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

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

  16. IF YOURS WORKS FOR THE FIRST CELL BUT NOT THE REST, CLICK ON THE FORMULA BAR OF THE FIRST CELL AND CLICK - CTR SHFT ENTER - THEN DRAG THAT FORMULA. THE FORMULA SHOULD NOW HAVE {funky brackets}.

  17. The one about Vlookup of multiple values is an absolute beauty. I have needed this function desperately and it worked exactly the way I have needed it. I don't know how to thank you. Still Thanks a million.

  18. Goal: I would like to vlookup or index match an ID from another workbook using a vba button that then returns the ID name, City, Region, etc. My goal is that I have a button I search the ID # and it returns the criteria that I want that is located in another worksheet. I know to start it with the Workbooks.Open more focused on how to do the index match or vlookup to return the criteria from that sheet. Thanks

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

    • 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)),"")

  20. You're the best! This saved me so many headaches, thank you so much!!

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