INDEX using INDIRECT with OR function

[brooksc29]

I am trying to use the INDEX function to refer to a range on another sheet and look for either of two criteria for a result.

This is my current formula and I'm off, just can't figure out where.

=INDEX(INDIRECT(C27),MATCH(OR("RHP-S","LHP-S"),INDEX(INDIRECT(C27),0,2),1))

Feel like I should be able to get this but I'm stuck.

Any help?

 

[Drrellik]

what is the name of the second sheet? I don't see it in your formula the two criteria are RHP-S and LHP-S what is the second sheet name and what is the range?

 

[Domenic]

Maybe...

=INDEX(INDIRECT(C27),MATCH(TRUE,ISNUMBER(MATCH(INDEX(INDIRECT(C27),0,2),{"RHP-S","LHP-S"},0)),0),1)

...confirmed with CONTROL+SHIFT+ENTER.

 

[brooksc29]

thanks Domenic, that seems to work.

what would I add to that formula to return the 2nd occurrence of either RHP-S/LHP-S in the column?

 

[Domenic]

thanks Domenic, that seems to work.

You're welcome!

what would I add to that formula to return the 2nd occurrence of either RHP-S/LHP-S in the column?

Are you only interested in the 2nd occurrence? Or are you in fact interested in all occurrences?

 

[brooksc29]

well, i would want all occurrences in descending order, down the column. so 2nd, 3rd, 4th, 5th, as you go down the column

 

[brooksc29]

OR, would it be easier to say, give me the 2nd occurrence anytime you see the "-S" text in the cell so it doesn't mark the 1st occurrence of "RHP-s", then go straight to the 2nd "LHP-S" occurrence without acknowledging the first occurrence of "LHP-S"?

if this is possible, it would actually work better for me to be able to look inside the text in the cell.

 

[brooksc29]

Drrellik, the range is actually represented in cell C27 - where it refers to a named range that is referenced by text in that cell.

 

[Domenic]

OR, would it be easier to say, give me the 2nd occurrence anytime you see the "-S" text in the cell so it doesn't mark the 1st occurrence of "RHP-s", then go straight to the 2nd "LHP-S" occurrence without acknowledging the first occurrence of "LHP-S"?

if this is possible, it would actually work better for me to be able to look inside the text in the cell.

Either way is fine. However, since you'd prefer to search for "-S", try...

A2:

=COUNTIF(INDEX(INDIRECT(C27),0,2),"*-S")

B2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(B$2:B2)<=$A$2,INDEX(INDIRECT($C$27),SMALL(IF(RIGHT(INDEX(INDIRECT($C$27),0,2),2)="-S",ROW(INDEX(INDIRECT($C$27),0,2))-MIN(ROW(INDEX(INDIRECT($C$27),0,2)))+1),ROWS(B$2:B2)),1),"")

 

[brooksc29]

I'm confused by the A2/B2, do I need to copy these in two different cells?