How to Vlookup to return multiple matches in Excel

The tutorial shows a few ways to Vlookup multiple values in Excel based on one or more conditions and return multiple matches 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 values 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 values in Excel

    Tip. To quickly get all different names in a list, you can use a formula to extract distinct values in Excel.
  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 copy the formula to a few more cells down the column (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 row 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.

This is how you Vlookup multiple matches in Excel using formulas. To have a closer look at the examples and probably reverse-engineer the formulas for better understanding, you are welcome to download the Excel Vlookup Multiple Values sample worksheet.

How to Vlookup to return multiple values in one cell

I will be upfront - I don't know a formula to Vlookup duplicates that would output multiple matches in a single sell. 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. If you don't have it in your Excel yet, you can download a free 14-day trial version here, and then follow the steps outlined 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 can be downloaded here. If the add-ins prove useful in your work, be sure to grab the 15% off coupon code that we provide exclusively to our blog readers: AB14-BlogSpo.

I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

128 Responses to "How to Vlookup to return multiple matches in Excel"

  1. Imran Masud says:

    Awesome:-), Svetlana. Carry on.

  2. Graham C. says:

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

  3. mohamad says:

    god gives you every thing you wish

  4. WGD says:

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

  5. Nawanto says:

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

    • Hello Nawanto,

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

      • alex says:

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

        • Hi Alex,

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

          • Uchay says:

            downloaded your source file from the link....

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

            how to display the formula result on the table?

            • Some guy says:

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

              Good luck.

      • Raj says:

        Hello Svetlana,

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

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

        I'm using Excel 2013.

        Regards,
        Raj

      • Kundan says:

        Hello Svetlana,

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

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

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

  6. ashfaqnasir says:

    thanks a lot

    very best activity to teach vlookup.

    regards

  7. salman says:

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

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

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

    Thanks & Regards,
    Salman

  8. Francis says:

    Awesome, incredibly fantastic

  9. Francis says:

    Excel makes life easy

  10. Shadab alam says:

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

  11. Nauman says:

    Very Informative, Great learning. Thank you :)

  12. Oscar says:

    Hi I need some help

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

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

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

      • Sundra says:

        Hi,

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

  13. Mafer says:

    Hello, I need some help...

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

  14. Ulises says:

    Hi Svetlana,

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

  15. Daniel Østermark says:

    Hi.

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

  16. Chinmay says:

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

  17. michael says:

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

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

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

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

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

    can anybody help

    cheers

  18. Imran says:

    Hi,

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

    Thanks

  19. Chris says:

    Svetlana,

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

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

  20. Vicki says:

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

  21. Anil says:

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

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

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

  22. Amy says:

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

  23. ULTEAR says:

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

  24. Rajesh Sinha says:

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

  25. Kamran says:

    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.

  26. Atul Sanwal says:

    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

  27. Manrique says:

    Inside the IF, why do you need ROW($B$3:$B$13)-2 ? Why not just write 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.

  28. Cherokee says:

    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!

    • Cherokee says:

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

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

  29. matching says:

    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!

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

  30. Khurram says:

    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.

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

  31. Joshua says:

    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.

  32. J R says:

    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.

  33. Celine says:

    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

  34. Ivan Linares says:

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

  35. DAVE. says:

    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.

  36. Kieran says:

    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!

  37. sudeep says:

    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.

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

  38. Skye says:

    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.

  39. Brent says:

    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

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

  40. Abdus Salam says:

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

  41. Remya says:

    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?

  42. Peter says:

    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

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

  43. Datta says:

    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.

  44. Govind Pal says:

    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.

    • Muhamed says:

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

  45. Govind Pal says:

    It is working. Its result is awesome.
    Thanks

  46. Muhamed says:

    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.

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

  47. ZJ says:

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

  48. Phil says:

    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.

  49. Brian says:

    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”

  50. JL says:

    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.

  51. SS says:

    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!

  52. LAKSHMI says:

    DOES THIS FORMULA WORK IN GOOGLE SHEETS?

  53. Adithya says:

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

  54. Basan says:

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

  55. Arthur says:

    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.

  56. NG says:

    =(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

  57. PakTam13 says:

    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

  58. Azhar says:

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

  59. Jules818 says:

    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!

  60. Aashish says:

    i want horizontal date range Vlookup pls. provide solution

  61. Div says:

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

  62. dkentaz65 says:

    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!

  63. Sam T says:

    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.

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

  64. Kevin P. says:

    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

  65. Sam T says:

    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

  66. Sanjin says:

    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

  67. Spiros says:

    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.

  68. Spiros says:

    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.

  69. Sam T says:

    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.

  70. Sylvain says:

    Awesome tutorial, thanks a lot!

  71. adee says:

    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

  72. yogendra singh thakur says:

    i want to apply the formula on a sheet.

    like i have criteria range and infront of value if my first criteria value is 0 than for same criteria v lookup should take the value for next criteria.

    Regards

    Yogendra

  73. Casey says:

    I have applied the array formula and it worked perfectly. I would now like to apply another formula to my listed results (in one column) in order to extract my distinct texts to the column next to it. My data from this formula has multiple texts vertically and I would like each unique data only repeated once (next to the first) in the next column and blanks following until the next unique text.

    Or just extracting the distinct values off of this formula.

    Is this possible?

    I look forward to your response. Thank you.

  74. bimal kumar sengupta says:

    am doing food gram as per menu for one person, when it is 100 person the gram reduce same items =IFERROR(VLOOKUP($B$6:$B$32,GRAM!$A$1:$B$300,2,FALSE)," 0 " )
    this formula will look out value in gram A1:A300 FOR SAY CHICKEN for one person , I NEED IT LOOK FOR MULTIPLE1 cells where i stored for 1 pax, 100 pax 200pax etc. it has diferent grams. kindly advise.

  75. Anthony says:

    How do I get the formula to search for match part of the string like APP instead of the full word APPLE?

  76. nikunj says:

    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 ?

  77. Muss says:

    Hi,

    I'm just wondering if Formula 3. Vlookup multiple matches based on multiple conditions can be used to retrieve data from other workbooks?
    I've tried however have not been successful.

    Many thanks

  78. Raj says:

    Well i have problem in using vlookup in pm mfsw worksheet

  79. Darwin Scheel says:

    I have a working formula to return multiple values from one Worksheet: "15".
    Formula =IFERROR(INDEX('15'!$A$7:$A$41,SMALL(IF($I$1='15'!$G$7:$G$41,ROW('15'!$G$7:$G$41)-MIN(ROW('15'!G$7:$G$41))+1,""),ROW()-2)),"")

    I would like the formula above to also go through each Worksheet and return multiple values.

    I have created a working table name "Sheet_Name" that will dynamically list Worksheets 1 through 100 starting with Sheet 6, currently I have Worksheets: A, B, C, D, E, and 1 - 15 (data to return).
    Sheet_Names Formula =OFFSET(SheetNames!$C$7,,,COUNT(SheetNames!$C7:$C106),1)

  80. Егор says:

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

  81. Nizam Mohammad says:

    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

  82. Erna says:

    HI
    I need help....
    I need an IF and V lookup formula but with multi-pal ranges.
    So IF A1 = DEL then look at Cell C1 to get the size and look at Cell D1 to get the area. Look up the area price according to the size of the container. Does that make any sense?

  83. Tyronne says:

    The worksheet table have columns (Cell B:Q)that show "Supplier Names" and the rows (15 to 33)have "Item01; Item02;and so on"
    What is required in Cell B2 if the "Item02" is typed-in, I want to find out the suppliers with the lowest 3 sales.

    Thank you, Look forward to your earliest response

  84. Riyaz Kachwalla says:

    Hi,

    I tried copy pasting the formula. Modified it for my specific columns and made sure to hit ctrl+shit+enter. The formula however only populates the first value (much like a regular VLOOKUP function. Can anyone please help?

    Thanks!

    Riyaz

  85. Cholpon says:

    This is great. Thanks a lot

  86. Lucas says:

    this really helped me alot, thank you!!

  87. Leah says:

    Hello,

    My vlookup is returning values that are not in the table array.

    Any suggestions.

  88. Jackie says:

    Hi

    Thanks very much for your explanations and tips, I've found them really useful. I used the Vlookup multiple conditions to return matches in a row and it works perfectly.

    As per your sample workbook, would there be a way of filtering out any duplicate values, for e.g. your return matches in a row for Seller Adam contains the Products 'Lemons' in both columns K and N - any way to stop that and just return one value of 'Lemons'?

    Many thanks

  89. Greg says:

    These formulas have worked for me but now I have a situation where I would just like a partial lookup match. Trouble is these formulas won't allow for wild cards like vlookup does. Is there a workaround for that?

    Example:
    =IF(ISERROR(INDEX($AQ$1:$AV$500,SMALL(IF($AQ$1:$AQ$500="REGULAR PAY 32.0000 ",ROW($AQ$1:$AQ$500)),ROW(1:1)),3)),"",INDEX($AQ$1:$AV$500,SMALL(IF($AQ$1:$AQ$500="REGULAR PAY 32.0000 ",ROW($AQ$1:$AQ$500)),ROW(1:1)),3))

    With this my "REGULAR PAY 32.0000 " must be an exact match when I'd just like to look for "REGULAR PAY*".

    I can do it with vlookup of course:
    =VLOOKUP("REGULAR PAY*",AQ1:AV500,3,FALSE)
    But of course it only returns the first value it finds.

  90. Farhan Ahmed says:

    Table 1 Table 2
    AMT result AMT Unique Value2
    30 A 30 A
    40 D 30 B
    30 B 30 C
    30 C 40 D
    50 F 40 E
    50 - 50 F

    I need to pick 2nd column of 'Table 2' by comparing AMT column but the formula should pick only one unique value 1 time from 'Table 2'

  91. Vivek Pandya says:

    It was really helpful. Thank you.

  92. Kim Feuerstein says:

    Thank you so much for this explanation--I'm not an Excel expert and this looked really intimidating, but I was able to make it work. I've bookmarked your site for future reference!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28