Need help with INDEX and MATCH

[pazuuu]

Hi,

Currently I have this formula: =INDEX($G$24:$G$129,MATCH("n",$F$24:$F$129,0))

I am looking for the next "n" match in column F. Please advise.

Thank you for any help given.

[XOR LX]

Hi.

Syntactically there's absolutely nothing wrong with your formula.

Or do you perhaps mean that you are looking for the first example where a cell contains the letter "n" anywhere within its contents, and not that the entire cell content is precisely "n"?

Regards

[pazuuu]

Hi,

Thanks for helping.

I am only only for exact "n" matches. "n" can be in any row in column f. The formula gives me the first match. I would like to find the subsequent matches (without using filter function), that excludes the 1st match.

Eg: 1st match => INDEX($G$24:$G$129,MATCH("n",$F$24:$F$129,0))
2nd match => formula to exclude 1st match
3rd match => formula to exclude 1st and 2nd matches
etc...

I am unsure of how to change the formula for this.

[XOR LX]

Put this formula in D23:

=COUNTIF(F24:F129,"n")

which counts the number of entries in the range F24:F129 which are equal to "n".

Then put this formula in your first cell of choice:

=IF(ROWS($1:1)>$D$23,"",INDEX($G$24:$G$129,AGGREGATE(15,6,ROW($F$24:$F$129)-MIN(ROW($F$24:$F$129))+1/($F$24:$F$129="n"),ROWS($1:1))))

and copy down until you start to get blanks for the results.

Regards

[pazuuu]

It's nearly working! I noticed that the input in Column G must be unique or it will give the previous first match.

E.g. Column F24: N, G24: Y
Column F46: N, G46: J
This set is shown correctly.

Column F24: N, G24: Y
Column F46: N, G46: Y
Only N,Y is given.
This result is used by another cell to indicate the staff category. So, Row 24: Teacher and Row 46: Lawyer. But I am only getting the result for Row 24.

Thank you for the help!

[XOR LX]

Sorry - not sure I understand what you mean.

Please re-upload a workbook with an example of this apparent issue.

Regards