CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
user2576682
user2576682

Reputation: 123

Excel formula to find third match not working

If have been trying to develop an excel formula to find matches for the nth value. I have been experimenting for about a week with no luck. The values I am trying to match are not sequential and I think that is my problem. I am hoping to get some assistance. In my formula I am trying to match values from multiple sheets. The value I am trying to match is in cell A7. In my second formula I have switched from LARGE to SMALL , IF I do that I get the same value as my index match formula.

Formula 1: works this finds the first match

=IFNA(INDEX(Consolidated!$A$2:$A$2661,MATCH($A7,Consolidated!$B$2:$B$1165,0),1),"")

Formula 2: This works and finds the second match =IFERROR(INDEX(Consolidated!$A$2:$A$1165,LARGE(IF(Consolidated!$B$2:$B$1165=$A7,ROW(Consolidated!$B$2:$B$1165)-1,""),COLUMNS($C$2:$C$1165))),"")

Formula 3 This does not work =INDEX(Consolidated!$A$2:$A$1165,MAX(IF(Consolidated!$B$2:$B$1165=$A7,ROW(Consolidated!$B$2:$B$1165)-ROW(A2)+2),2))

Upvotes: 0

Views: 115

Answers (1)

Apostolos55
Apostolos55

Reputation: 580

try =SMALL(IF($B$7:$B$27=$A$7,ROW($B$7:$B$27),"99"),ROW(OFFSET($A$1,,,$A$6)))

a6: number of results

a7: item you look for

$B$7:$B$27: range to look into (may add sheet as well)

ROW($B$7:$B$27): return item if found! here returns the row

ROW(OFFSET($A$1,,,$A$6)): get array of values 1 -> number of results

mind that the entire code must be written as an Array (with ctrl+shft+enter) to as many cells (rows) as max expected results (in A6). Also remember that after being made, arrays can only grow bigger!

to get only the nth result all you do is replace the "ROW(OFFSET($A$1,,,$A$6))" with the "n" and write as array.

this is my example with the results enter image description here

Upvotes: 0

Related Questions