Index match minimum 2 matches or more with a single formula , maybe using indexe
ID: 3560925 • Letter: I
Question
Index match minimum 2 matches or more with a single formula , maybe using indexed value as wildcard values
hello
I recently learned the Index match and find it very useful, so far I know how to index match with multiple criteria,
but I want a single index match formula to work whenever there is more than certain numbers of criteria
for example , below works fine and I know how to
indexed table
A B C D E
1 1 1 1 ONE
2 2 2 2 TWO
3 3 3 3 THREE
4 4 4 4 FOUR
5 5 5 5 FIVE
6 6 6 6 SIX
7 7 7 7 SEVEN
8 8 8 8 EIGHT
9 9 9 9 NINE
formula
= INDEX($A$1:$E$9,MATCH(1,(K4=$A$1:$A$9)*(L4=$B$1:$B$9)*($M$4=C1:C9),0),5)
result
CRITERIA1 CRITERIA2 CRITERIA3 RESULT ( this cell is where the excel formula is)
2 2 2 TWO
but above formula works only when 3 criteria are perfect match with the indexed value,
but I want above formula to work on cases like below where the case requires minimum 2 matches
result
CRITERIA1 CRITERIA2 CRITERIA3 RESULT ( this cell is where the excel formula is)
2 2 2 TWO
random 3 3 three
4 random 4 four
(random : anything can come)
indexed table
A B C D E
1 1 1 1 ONE
2 2 2 2 TWO
3 b 3 3 THREE
4 4 b 4 FOUR
5 5 5 5 FIVE
6 6 6 6 SIX
7 7 7 7 SEVEN
8 8 8 8 EIGHT
9 9 9 9 NINE( b stands for blank)
I could do it by using multiple index match with 3criteria & 2criteria & 2criteria ( with different lookup cell),
but it takes too much time to calculate, therefore impossible to practice it in reality, ( lags too heavily )
I thought if i can put an wild card value at "b" in indexed table, i could solve it with single formula, but I could not find the way
if possible i would put wild card value for any value in cell "b" so that the formula gets the result whenever there is a match
with the minimum 2 criteria which is only condtion that matters.
does anybody know how I can put wildcard values in indexed table or how I can solve the problem ?
Explanation / Answer
(.If it is a matter of handling blanks, following Array Formula can be used -=-
= INDEX($A$1:$E$9,MATCH(1,(K4=IF($A$1:$A$9="",K4,$A$1:$A$9))*(L4=IF($B$1:$B$9="",L4,$B$1:$B$9))*($M$4=IF($C$1:$C$9="",M4,$C$1:$C$9)),0),5)
You need to tweak the above formula as per your need.@@