INDEX MATCH – multiple results
Table of Contents
1. INDEX and MATCH - multiple criteria and multiple results
This section demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is in cell E3, the lookup range is B3:B8.
Cells B3, B5, and B8 contains the lookup value, cell values in the corresponding cells in column C are returned. They are C3, C5, and C8.
There is actually a smaller formula that does the same thing: VLOOKUP - Return multiple values I also recommend the FILTER function if you are an Excel 365 user, the FILTER function is really easy to use.
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the value in cell E3.
The matching rows are 3, 5 and 8 so the array formula returns 3 values in cell range E6:E8.
The formula above is an array formula, make sure you follow the instructions below on how to enter an array formula to make it work.
1.1 How to enter an array formula
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Now copy cell E6 and paste to cells below as far as needed.
1.2. Explaining formula in cell E6
Step 1 - Find matching values
The MATCH function matches a cell range against a single value returning an array.
MATCH($B$3:$B$8, $E$3, 0)
becomes
MATCH({"A"; "B"; "A"; "C"; "B"; "A"}, "A", 0)
and returns
{1; #N/A; 1; #N/A; #N/A; 1}.
If a value is equal to the search value MATCH function returns 1. If it is not equal the MATCH function returns #N/A.
The picture above displays the array in column A.
Step 2 - Convert array values to boolean values
The IF function cant process error values so to solve that I am going to use the ISNUMBER function to convert the array values to boolean values.
ISNUMBER(MATCH($B$3:$B$8, $E$3, 0))
becomes
ISNUMBER({1; #N/A; 1; #N/A; #N/A; 1})
and returns
{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}.
The array is shown in column A, see picture below.
Step 3 - Identify rows
The IF function converts the boolean values into row numbers and blanks.
becomes
The MATCH and ROW functions calculate an array with sequential numbers, 1 to n, determined by the size of the cell range. In this case, $B$3:$B$8 has 6 values so the array becomes 1 to 6.
and returns
{1;"";3;"";"";6}.
The picture below shows the relative row numbers for cell range B3:B8.
Step 4 - Get the k-th smallest row number
To be able to return the correct value the formula must know which value to get. The SMALL function determines the value to get based on row number.
becomes
The ROWS function returns a number that changes when you copy the cell and paste to cells below.
and returns 1.
In the next cell below ROWS($A$1:A1) changes to ROWS($A$1:A2) and returns 2.
Step 5 - Get values from column C using row numbers
The INDEX function returns a value from a given cell range based on a row and column number.
becomes
The first cell value in cell range $C$3:$C$8 is 6, the INDEX function returns 6 in cell E6.
1.3. Get Excel file
2. INDEX and MATCH - multiple criteria and multiple results
This section demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel 365 formula shown in section 2 is incredibly small, the new FILTER function is amazing.
The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied to column Color.
This formula can only retrieve one value per criteria, read this article to extract multiple values per criteria.
This should be an array formula, however, the second INDEX function makes this formula a regular formula.
2.1 Explaining formula in cell C14
Step 1 - Find relative position of specified conditions in C12:D12
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH($C$12:$D$12, $B$3:$B$10, 0)
returns {3, 2}.
Step 2 - Get value based on the relative position
The INDEX function returns a value from a cell range, you specify which value based on a row and column number. However, in this case, it's used to convert the formula to a regular formula.
This is a workaround and it won't work in some array formula, it works fine in this one.
INDEX(array, [row_num], [column_num], [area_num])
INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), )
returns {3, 2}.
Step 3 - Calculate k-th smallest number
The SMALL function returns the k-th smallest value from a group of numbers.
SMALL(array, k)
SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1))
becomes
SMALL({3, 2}, ROWS($A$1:A1))
The ROWS function counts the number of rows in a given cell reference, however, $A$1:A1 is a cell reference that grows automatically when you copy the cell and paste it to cells below. This makes the formula return a new value in each cell.
SMALL({3, 2}, ROWS($A$1:A1))
returns 2.
Step 4 - Get value
The INDEX function returns a value from a cell range, you specify which value based on a row and column number. However, in this case, it's used to convert the formula to a regular formula.
This is a workaround and it won't work in some array formula, it works fine in this one.
INDEX(array, [row_num], [column_num], [area_num])
INDEX($C$3:$C$10, SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1)))
returns "C" in cell C14.
3. INDEX and MATCH - multiple criteria and multiple results - Excel 365
The new FILTER function is amazing, it returns multiple values based on boolean value TRUE or FALSE or their numerical equivalents.
Dynamic array formula in cell G3:
Excel 365 returns arrays automatically and deploys values to adjacent cells as far as needed, Microsoft calls this behavior "spilling".
Explaining formula in cell G3
Step 1 - Count values based on criteria
The COUNTIF function counts values based on a condition or criteria.
COUNTIF(range, criteria)
COUNTIF(E3:E4, B3:B10)
returns {0; 1; 1; 0; 0; 0; 0; 0}.
Step 2 - Get values
The FILTER function lets you extract values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER(C3:C10, COUNTIF(E3:E4, B3:B10))
returns {"C"; "F"}.
Get Excel *.xlsx file
INDEX and MATCH - multiple criteria and multiple results.xlsx
Index match category
Table of Contents Lookup multiple values across columns and return a single value Lookup using multiple conditions Lookup a date […]
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
Excel categories
35 Responses to “INDEX MATCH – multiple results”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
I'm building a workbook to search for any results that may use up to 34 criteria. So far I've built a formula from the website to fill six criteria, and I've hit a snag. The formula is creating duplicates. I want to avoid creating a list of results with duplicate values, then building a separate formula to create a list of unique values. Is there a way to do that all in one formula?
Here's the formula: =INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1)))
Name = B3:B59
Category = AH3:AM59
Justin,
Yes, it is possible. If you enter the formula in cell F2 the formula becomes:
=INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category)*(COUNTIF($F$1:F1,Category)=0), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1)))
Any tips for doing this if theres multiple pairs of columns? Is it possible to concatenate results from multiple formulas of this kind into one column. For example lets say you were searching over 'n' pairs of the "text" and "amount" columns side by side, but still wanted the search results in a single column, like you have?
Joe Elizondo,
Yes, it is possible.
Array formula in cell C11:
=IFERROR(INDEX($C$3:$C$7, SMALL(IF(ISNUMBER(MATCH($B$3:$B$7, $C$9, 0)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))), INDEX($F$3:$F$7, SMALL(IF(ISNUMBER(MATCH($E$3:$E$7, $C$9, 0)), MATCH(ROW($E$3:$E$7), ROW($E$3:$E$7)), ""), ROWS($A$1:A1)-COUNTIF($B$3:$B$7, $C$9))))
Hi Oscar,
This example helped me to get closer to what I am looking for, but not completely yet :).
In my case, I am looking to retrieve the sum of the results returned in a single cell.
First of all, I have converted your formula to column based:
The formula used is:
={IFERROR(INDEX($C$14:$L$14,SMALL(IF(ISNUMBER(MATCH($C$13:$L$13,C$16,0)),MATCH(COLUMN($C$13:$L$13),COLUMN($C$13:$L$13)),""),ROWS($A$1:A1))),0)}
Text A G E C E A B G C C
Amount 2 4 1 3 2 3 1 3 1 2
Search A B C D E F G H
Results 2 1 3 0 1 0 4 0
3 0 1 0 2 0 3 0
0 0 2 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0
SUM 5 1 6 0 3 0 7 0
As you see I have to create many rows to find all matches of each value. In my real case, I have more than 100 columns to match, and I don't know how many matches I will have. So I look for a way to return the sum of all matches (A - H) in a single row.
I am also working on a solution within VBA as back-up.
I hope you can help me. Please feel free to contact me if you need additional information.
Kind regards,
Jorgen
Hi Jorgen
Try this:
Formula in cell J3:
=SUMPRODUCT((B2:G13=I3)*1)
Hi Oscar,
This was super useful, thanks! I'm trying to take this one step further and be able to return all match instances of a certain value while having to search through more than a single-column array. To work through this using your example, I added a second column of Amounts and modified your formula to look up a given Amount and return the Text values that match that Amount. I got this to work with your INDEX(SMALL(IF(ISNUMBER(MATCH())))) and can pull all of the Text values from both Amount columns. I've also managed to return only Text values with that Amount from Amount2 using INDEX(MATCH(INDEX(MATCH))), however this can only find the first instance in the array. What I'm really trying to do is a combination of these: return all of the Text values within the given Amount array, while narrowing the search to a specific column within the array. Do you have any tips for this?
Formula in cell F5
=INDEX($B$2:$B$14, SMALL(IF(ISNUMBER(MATCH($C$2:$D$14, $F$2, 0)), MATCH(ROW($C$2:$D$14), ROW($C$2:$D$14)), ""), ROWS($A$1:A1)))
Formula in cell G5
=INDEX($B$2:$B$14, MATCH($F$2, INDEX($C$2:$D$14, 0, MATCH($G$2, $C$1:$D$1,0)),0))
Thanks,
Jeremiah
Jeremiah,
I believe you are looking for this formula:
https://www.get-digital-help.com/2011/06/22/vlookup-a-range-in-excel/
Oscar,
Thanks for the reply. It looks like my image link didn't come through, trying again here:
https://imgur.com/a/FjajCCc
The article you referenced is close to what I'm looking for, but it doesn't allow me to narrow my search within the array to return all matches from only one desired column. Hopefully my example in the screenshot linked above will clarify this, the objective in cell H4 is what I'm trying to figure out a formula for.
Best,
Jeremiah
Jeremiah,
Formula in cell B14:
=INDEX($B$3:$B$6, SMALL(IF((INDEX($C$3:$E$6, 0, MATCH($C$10,$C$2:$E$2, 0))=$C$9)*(COUNTIF($B$13:B13, $B$3:$B$6)=0), ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, ""), 1))
Hi Oscar,
Thank you very much for your article, it's very useful!
Regarding the last formula you've sent to Jeremiah, I'm also trying to have it working in a similar way but haven't been successful.
Basically instead of a match between a column and a row, I'm trying for the match of two columns to return the corresponding cells of a 3rd column.
Can you help me on this? Should I send what I have until now?
Best,
João
Table-1 Table-2
Number Name Number Name
10 A 10 search with Search result
11 B 13 C 13 #REF!
13 16 F
14 D 19 I
15 E 21 K
16
17 G
18 H
19
20 J
I have used this formula =+INDEX($D$3:$D12,MATCH($H4,$C$3:$C12,INDEX($G$3:$G7,MATCH($H4,$F$3:$F7,0))))
but is not working
I want there as 'C' because in Table-1 13 corresponding nothing so the formula should check with Table-2 with and result will be 'C'
Advance thanks
Hey Good Day,
can u do it if you have multiple person in same organization like
Org | Name | Badge | GC
649238 Rayn 64982 08
649238 Jhon 78421 11
649238 sara 76899 06
when i setup it with index match it gives me Rayn duplicated.
Rayn
The following link takes you to an article that demonstrates how to extract records based on a lookup value:
https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#multiple
Thanks for the Tip, it already helped a lot, i just have the Problem, that my matches are not sorted from the earliest to the latest date. I don´t know why, because with the "SMALL" Funktion it should sort my matches by date beginning from the earliest date?
Flo
If column c contains dates that you want to extract based on a lookup value then use this array formula in cell E6:
=SMALL(IF($E$3=$B$3:$B$8, $C$3:$C$8, ""),ROWS($A$1:A1))
More examples here:
https://www.get-digital-help.com/2013/04/23/small-function-and-large-function/
This article demonstrates how to use multiple conditions:
https://www.get-digital-help.com/2017/12/15/small-function-multiple-criteria/
Oscar,
Your original set up almost answers my question, but I need to take it one step further. Instead of creating 3 rows in order to return the 6,4 & 1 values, I would like to sum all three into one cell. How do I do that.
https://i.postimg.cc/dVSffxhx/excell-1.jpg
https://i.postimg.cc/FHfpYsKT/excel2.jpg
So in my example, I would like to look up "B", and then lookunder the month of January, and sum up the number of "B"'s that is returned under that column. I just want the one row fo rth esum of B's. I dont want to have multiple cells that I still have to solve for, and I dont want to hid cells. Under the Cell for B, I might want to look up the Number of any letter in a particular month.
Does that make sense?
Thanks
Mike.
"Now copy cell E6 and paste to cells below as far as needed."
I would like to use data validation to allow the results to appear in a drop-down list in cell E6, instead of using the copy/paste instructions above. Is this possible?
Kyle
Yes, it is possible, however, you still need to extract the values using the formula perhaps on a different sheet.
I recommend a dynamic named range: Create a dynamic named range
Hello Oscar!
I'm new to excel and i just got employed in a firm that uses it a lot and im dealing with complex stuff so i really need some help.
I'm trying to make a table that will show me all the offers my employer made or is making by their status (i.e. in preparation, accepted, rejected etc).
This is the main table of offers https://imgur.com/1ilGTl0
What i need is: using the value in the right most column R(0-7) which shows the status of the offer, to get in,a new table, the left most column (column A) value (70-p/2019,72-p/2019) in an order without entering all that manually like im doin right now as shown in https://imgur.com/t8Kfp9N
I hope i was clear enough and that you can help me.
Looking forward to your response
Any idea why my formula is returning #N/A instead of the value I want? I am pretty sure it is working as intended because it should return 6 values and it is returning 6 #N/A and then #value. I am stumped. Is col A above a helper column? or is it all contained in the formula? Any assistance trouble shooting would be much appreciated!
Also, do you have any tips on how to write this for two variables? i.e return the corresponding customer if the variable is 5 or 6?
Adam,
Any idea why my formula is returning #N/A instead of the value I want?
Make sure the cell references are correct. You need to enter the formula as an array formula.
Is col A above a helper column?
No
or is it all contained in the formula?
Yes
Any assistance trouble shooting would be much appreciated!
I recommend you use "Evaluate Formula" tool found on tab "Formulas" on the ribbon.
Oscar, thanks so much! I really appreciate your assistance, the issue was the array formula, I overlooked that part in the initial article.
Thanks again!
what is wrong in this formula.
show #value
but in your excel sheet it shows correctly
=INDEX($L$2:$L$41,SMALL(IF(ISNUMBER(MATCH($M$2:$M$41,A2,0)),MATCH(ROW($M$2:$M$41),ROW($M$2:$M$41)),""),ROWS($A$1:A1)))
Thank you for publishing this wonderful guide. I am having one problem with it though. The formula returns the correct number of matches but each match listed is the first cell (cell O2) referenced in the index function. I need it to return the information matched for each cell from row 2.
=INDEX('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$2:$BS$2,SMALL(IF(ISNUMBER(MATCH('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$5:$BS$5,$B$7,0)),MATCH(ROW('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$5:$BS$5),ROW('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$5:$BS$5)),""),ROWS('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$A$1:A1)))
Hi, I need to introduce a wildcard to this formula:
From this...
MATCH($B$3:$B$8, $E$3, 0)
To this...
MATCH($B$3:$B$8, $E$3&"*", 0)
But doing so breaks the formula. Do you have a workaround for this?
Hi Oscar, you are such a great resource. I was rocking right along until i copied the formula to the 12th column I received the #Num! error. Maybe I don't understand the ",ROWS($A$1:A1))),0)}" function. My data resides on two other sheets here is my formula:
=INDEX(STUDENT_A!$A$2:$A$6335, SMALL(IF(ISNUMBER(MATCH(STUDENT_A!$I$2:$I$6335, X$1, 0)), MATCH(ROW(STUDENT_A!$I$2:$I$6335), ROW(STUDENT_A!$I$2:$I$6335)), ""), ROWS(STUDENT_A!$A$1:$A1)))
Please advice. thanks
I got the exact same issue, also drawing from an external sheet. Any ideas?
Hello.
Thank you for publishing those awesome tutorials.
I am having a little problem here - I would like to test for the regular expression value (value you provided here in $E$3). It seems not to work.
I was trying to break the formulas to simple steps and everything seems to work fine if the value in the cell is exact match. Regular expressions seem to be treated as normal typing.
Do you know anything about it, maybe know a workaround?
Jakub
I believe you need VBA to be able to use regular expressions: https://www.get-digital-help.com/like-operator/
Hi Oscar,
Hoping you can help,
I'm trying to do an index and match multiple results across 2 groups with over 14,000 values to match.
I've tried to show an example below of what I'm trying to create.
I need to index a list of nested groups and users in sheet 1. Then match the group name they are a member of in sheet 2 with the same name in sheet 1 so that these values are pulled though to sheet 2 where the new group name is in a separate column.
DLG Domain 2 Returned Nested Groups`Users
DLG_1 Domain1\GG_Group
DLG_1 Domain2\GG_Group
DLG_1 User1
DLG_1 User2
DLG_2 Domain1\GG_Group
DLG_2 User1
DLG_2 User2
DLG_3 Domain1\GG_Group
DLG_3 Domain2\GG_Group
DLG_3 User1
DLG_3 User2
DLG_3 User3
Paul Quirie,
I think you are looking for this: https://www.get-digital-help.com/lookups-in-a-related-table-array-formula/
Dear Professor Oscar
I was wondering if there is a possibility, to simply ignore all text or blank cells and only search the maximum number value with the criteria in one column. It would also need the formula to be able to evaluate a cell which has mixed contents, like: "V20-001"
Here is the sample
Voucher criteria Result in Max Result in Min
V20-001 V20* V20-500 V20-001
X20-001 X20* X20-500 X20-001
V20-003
X20-100
V20-500
X20-500
X20-002
V20-002
I tried to use this formula:-=INDEX(A8:A51878,MATCH(2,1/SEARCH(A5,A8:A51878))) but it is not work.
Thank you very much.
Best regards,
Grace
I have app list with corresponding wave list. Each app can belong to multiple wave
Eg:
app1 Wave1
app1 Wave2
app2 Wave1
app1 Wave5
app1 Wave6
I am looking for result like this
app1 wave1, wave2, wave5, wave6...
The formula in this tutorial is to do multiple match function but i dont have definite wave number. each app can belong to 1 or 10 wave. its kind of like looping through wave and finding a match. so far I just have index & match function in 4 and more cells which is not very clean. any assitance appreciated. Thanks
Hi Oscar,
Thanks for this great article; it has helped me a lot!
Is there a way to pull multiple results from 12 different tables?
I have a table for each of the 12 months, and I need to pull specific data (based on a single criterion) from all 12 tables into one annual table.
I saw your reply to Joe regarding two different tables, but I couldn't figure out how to modify it for 12 different tables.
Any help is appreciated; thanks!