I am trying to write an Index Match formula that will return each subsequent occurrence of the Match criteria. The Match criteria is 2 part, contains 2 different variables to match.
I have a formula to return the 1st occurrence of matching the 2 separate criteria. It is,
{=MATCH(1,($Q$20=$F$2:$F$500)*(AG$1=$B$2:$B$500),0)}
When I put that part in to the following formula it returns the first occurrence of the matched criteria,
{=IF(ISNA(INDEX($B$2:$D$500,MATCH(1,($F3=$B$2:$B$500)*(G$1=$A$2:$A$500),0),3)),"",INDEX($B$2:$D$500,MATCH(1,($F3=$B$2:$B$500)*(G$1=$A$2:$A$500),0),3))}
In order to return subsequent values I believe I should use a formula like the following containing the Small function using the Row function in tandem,
{=INDEX($C$3:$C$7,SMALL(IF(($B$10=$B$3:$B$7),MATCH(ROW($B$3:$B$7),ROW($B$3:$B$7)),""),ROWS($A$1:A1)))}
How would I combine these 2 ideas to achieve my goal?
The attached workbook contains the data set and the desired outcome. The cells highlighted in yellow are the subsequent values that I need the formula to return. The second formula is what I am using in the workbook, the third formula is just an example of a working formula of the type I think I need to use.
Bonus, my results need to appear horizontally instead of vertically. I believe there is a way to do that by using Column instead of Row in the last part of the third formula.
Thanks,
Bookmarks