INDEX MATCH against value in the list.

C

ceecee88

Board Regular
Joined
Jun 30, 2022
Messages
59
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hi, here is what I intended to do using INDEX ad MATCH.
1. I would like to show the whole row result. Example in yellow highlight, input formula in I9 and it show the rest of the info in the row (like array result).
2. There are multiple matching conditions and in each condition there are more than 1 value to match (list of value is dynamic, sometime there is only 1 value or sometime there can be 4 values in each list).
3. There could be more than 1 result.

So far I'm able to match multiple condition and return multiple result using the formula below (in green highlight), but I'm not able to match more than 1 value in each condition and return the whole row, is it possible to do that? Any suggestion will be much appreciated.

={IFERROR(INDEX($C$2:$C$16,SMALL(IF((INDEX($C$2:$G$16,,4)=$J$3)*(INDEX($C$2:$G$16,,5)=$M$3),MATCH(ROW($C$2:$G$16),ROW($C$2:$G$16)),""),ROWS(I$17:I17)),COLUMNS($A$1:$A1)),"")}

Note: I'm using Excel 2013, so any fancy 365 formula won't work.

Thank you in advance for your help.

1687760169046.png
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Book2
BCDEFGHIJKLM
1IDNameAgeGenderInterestLocationSearch for
21Name 120MMovieArea 1
32Name 222FGameArea 5InterestMovieLocationArea 1
43Name 325FMovieArea 2TV ShowArea 2
54Name 435FTV ShowArea 1Area 3
65Name 521MGameArea 3
76Name 642MTV ShowArea 2Result
87Name 752MBookArea 5
98Name 832FBookArea 4Name 120MMovieArea 1
109Name 926FTV ShowArea 2Name 325FMovieArea 2
1110Name 1028FTV ShowArea 5Name 435FTV ShowArea 1
1211Name 1129FGameArea 3Name 642MTV ShowArea 2
1312Name 1227MGameArea 5Name 926FTV ShowArea 2
1413Name 1362FMovieArea 1Name 1362FMovieArea 1
1514Name 1445FBookArea 3
1615Name 1565MBookArea 4
Sheet1
Cell Formulas
RangeFormula
I9:M14I9=FILTER(C2:G16,(G2:G16=M3)+(G2:G16=M4)+(G2:G16=M5)*(F2:F16=J3)*(F2:F16=J4))
Dynamic array formulas.


Excel Formula: 
=FILTER(C2:G16,(G2:G16=M3)+(G2:G16=M4)+(G2:G16=M5)*(F2:F16=J3)*(F2:F16=J4))

Are you able to use Filter Function?
 
Upvote 0
RudRud said:
Book2
BCDEFGHIJKLM
1IDNameAgeGenderInterestLocationSearch for
21Name 120MMovieArea 1
32Name 222FGameArea 5InterestMovieLocationArea 1
43Name 325FMovieArea 2TV ShowArea 2
54Name 435FTV ShowArea 1Area 3
65Name 521MGameArea 3
76Name 642MTV ShowArea 2Result
87Name 752MBookArea 5
98Name 832FBookArea 4Name 120MMovieArea 1
109Name 926FTV ShowArea 2Name 325FMovieArea 2
1110Name 1028FTV ShowArea 5Name 435FTV ShowArea 1
1211Name 1129FGameArea 3Name 642MTV ShowArea 2
1312Name 1227MGameArea 5Name 926FTV ShowArea 2
1413Name 1362FMovieArea 1Name 1362FMovieArea 1
1514Name 1445FBookArea 3
1615Name 1565MBookArea 4
Sheet1
Cell Formulas
RangeFormula
I9:M14I9=FILTER(C2:G16,(G2:G16=M3)+(G2:G16=M4)+(G2:G16=M5)*(F2:F16=J3)*(F2:F16=J4))
Dynamic array formulas.


Excel Formula: 
=FILTER(C2:G16,(G2:G16=M3)+(G2:G16=M4)+(G2:G16=M5)*(F2:F16=J3)*(F2:F16=J4))

Are you able to use Filter Function?
Click to expand...
Hi, thank you for a quick reply, but FILTER won't work in 2013 and the value in the list is dynamic so I can't do a fix condition like that.
 
Upvote 0
ceecee88 said:
Hi, thank you for a quick reply, but FILTER won't work in 2013 and the value in the list is dynamic so I can't do a fix condition like that.
Click to expand...

Ahh i see, might need to wait other Gurus :)
 
Upvote 0
See if this does what you want.

23 06 26.xlsm
BCDEFGHIJKLMNOPQRS
1IDNameAgeGenderInterestLocationSearch for
21Name 120MMovieArea 1
32Name 222FGameArea 5InterestMovieLocationArea 1Result
43Name 325FMovieArea 2TV ShowArea 2Name 120MMovieArea 1
54Name 435FTV ShowArea 1Area 3Name 325FMovieArea 2
65Name 521MGameArea 3Name 435FTV ShowArea 1
76Name 642MTV ShowArea 2Name 642MTV ShowArea 2
87Name 752MBookArea 5Name 926FTV ShowArea 2
98Name 832FBookArea 4Name 1362FMovieArea 1
109Name 926FTV ShowArea 2     
1110Name 1028FTV ShowArea 5     
1211Name 1129FGameArea 3     
1312Name 1227MGameArea 5     
1413Name 1362FMovieArea 1     
1514Name 1445FBookArea 3     
1615Name 1565MBookArea 4     
Sheet2 (2)
Cell Formulas
RangeFormula
O4:S16O4=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$16)/(ISNUMBER(MATCH($F$2:$F$16,$J:$J,0))*ISNUMBER(MATCH($G$2:$G$16,$M:$M,0))),ROWS(O$4:O4))),"")
 
Upvote 0
Peter_SSs said:
See if this does what you want.

23 06 26.xlsm
BCDEFGHIJKLMNOPQRS
1IDNameAgeGenderInterestLocationSearch for
21Name 120MMovieArea 1
32Name 222FGameArea 5InterestMovieLocationArea 1Result
43Name 325FMovieArea 2TV ShowArea 2Name 120MMovieArea 1
54Name 435FTV ShowArea 1Area 3Name 325FMovieArea 2
65Name 521MGameArea 3Name 435FTV ShowArea 1
76Name 642MTV ShowArea 2Name 642MTV ShowArea 2
87Name 752MBookArea 5Name 926FTV ShowArea 2
98Name 832FBookArea 4Name 1362FMovieArea 1
109Name 926FTV ShowArea 2     
1110Name 1028FTV ShowArea 5     
1211Name 1129FGameArea 3     
1312Name 1227MGameArea 5     
1413Name 1362FMovieArea 1     
1514Name 1445FBookArea 3     
1615Name 1565MBookArea 4     
Sheet2 (2)
Cell Formulas
RangeFormula
O4:S16O4=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$16)/(ISNUMBER(MATCH($F$2:$F$16,$J:$J,0))*ISNUMBER(MATCH($G$2:$G$16,$M:$M,0))),ROWS(O$4:O4))),"")
Click to expand...
Hi, yes this will definitely get the multiple value match for each condition. Is there anyway to show the each result for the whole row? Instead of having to put in the formula for each column?

I tried this and the result came for the whole row but obviously it only work for 1 value for each condition.
=IFERROR(INDEX($C$2:$F$16,SMALL(IF((INDEX($C$2:$G$16,,4)=J3)*(INDEX($C$2:$G$16,,5)=$M$3),MATCH(ROW($C$2:$G$16),ROW($C$2:$G$16)),""),ROWS(A$1:A1)),0),"")

Thank you!
 
Upvote 0
I don't have 2013 to test but does this do it? (Mind you, not very hard to copy the formula across and down. ;))

23 06 26.xlsm
CDEFGHIJKLMNOPQRS
1NameAgeGenderInterestLocationSearch for
2Name 120MMovieArea 1
3Name 222FGameArea 5InterestMovieLocationArea 1Result
4Name 325FMovieArea 2TV ShowArea 2Name 120MMovieArea 1
5Name 435FTV ShowArea 1Area 3Name 325FMovieArea 2
6Name 521MGameArea 3Name 435FTV ShowArea 1
7Name 642MTV ShowArea 2Name 642MTV ShowArea 2
8Name 752MBookArea 5Name 926FTV ShowArea 2
9Name 832FBookArea 4Name 1362FMovieArea 1
10Name 926FTV ShowArea 2 
11Name 1028FTV ShowArea 5 
12Name 1129FGameArea 3 
13Name 1227MGameArea 5 
14Name 1362FMovieArea 1 
15Name 1445FBookArea 3 
16Name 1565MBookArea 4 
ceecee88 (2)
Cell Formulas
RangeFormula
O10:O16,O4:S9O4=IFERROR(INDEX(C:G,AGGREGATE(15,6,ROW(C$2:C$16)/(ISNUMBER(MATCH($F$2:$F$16,$J:$J,0))*ISNUMBER(MATCH($G$2:$G$16,$M:$M,0))),ROWS(O$4:O4)),0),"")
Dynamic array formulas.
 
Upvote 0
Solution
Peter_SSs said:
I don't have 2013 to test but does this do it? (Mind you, not very hard to copy the formula across and down. ;))

23 06 26.xlsm
CDEFGHIJKLMNOPQRS
1NameAgeGenderInterestLocationSearch for
2Name 120MMovieArea 1
3Name 222FGameArea 5InterestMovieLocationArea 1Result
4Name 325FMovieArea 2TV ShowArea 2Name 120MMovieArea 1
5Name 435FTV ShowArea 1Area 3Name 325FMovieArea 2
6Name 521MGameArea 3Name 435FTV ShowArea 1
7Name 642MTV ShowArea 2Name 642MTV ShowArea 2
8Name 752MBookArea 5Name 926FTV ShowArea 2
9Name 832FBookArea 4Name 1362FMovieArea 1
10Name 926FTV ShowArea 2 
11Name 1028FTV ShowArea 5 
12Name 1129FGameArea 3 
13Name 1227MGameArea 5 
14Name 1362FMovieArea 1 
15Name 1445FBookArea 3 
16Name 1565MBookArea 4 
ceecee88 (2)
Cell Formulas
RangeFormula
O10:O16,O4:S9O4=IFERROR(INDEX(C:G,AGGREGATE(15,6,ROW(C$2:C$16)/(ISNUMBER(MATCH($F$2:$F$16,$J:$J,0))*ISNUMBER(MATCH($G$2:$G$16,$M:$M,0))),ROWS(O$4:O4)),0),"")
Dynamic array formulas.
Click to expand...
Hi, thank you! this is exactly what I'm looking for.

I'm not concern about dragging the formula to the side and down, but I'm thinking if I have to use this formula many times in the sheet, the array would be faster for the calculation VS each cell has it own formula to calculate?

For example, if I have 10 separated areas in the sheet that need this formula (10 areas, 5 rows and 5 columns each) Excel has to calculate 250 times, but if we use array formula it only calculate for 50 time/cells (10 areas by 5 rows). Or may be my thought is totally wrong and Excel use the same time for calculation, Or even worse - array formula use more time? You do happen to know the different in calculation time in for these 2 formula?

Again, thank you for much for your help, I really appreciate it.
 
Upvote 0
ceecee88 said:
You do happen to know the different in calculation time in for these 2 formula?
Click to expand...
No, not for sure. With that small sample data there is basically no difference and I don't have relevant large data to test.
If you have large data then you can test yourself and if you cannot easily tell the difference then it doesn't matter.

ceecee88 said:
Again, thank you for much for your help, I really appreciate it.
Click to expand...
You're welcome. Glad to help. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
477
Peter_SSs
Peter_SSs
D
  • Solved
Vlookup/Index that returns all distinct values, but returns in a row?
Replies
8
Views
405
Fluff
Fluff
A
  • Question
Index and Match for 2No criteria only partially returning values other values #ref error.
Replies
6
Views
144
roelandwatteeuw
R
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
301
Peter_SSs
Peter_SSs
C
  • Question
Index Match question
Replies
2
Views
87
Cubist
Cubist

Forum statistics

Threads
1,215,152
Messages
6,123,323
Members
449,094
Latest member
Chestertim

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top