# Index Match based on multiple Match criteria that returns each subsequent occurence.

1. ## Index Match based on multiple Match criteria that returns each subsequent occurence.

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,

2. ## Re: Index Match based on multiple Match criteria that returns each subsequent occurence.

``Please Login or Register  to view this content.``
Drag down and accross

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1