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?

 

[brooksc29]

so with the "-S" formula, I am using this and it is working
=INDEX(INDIRECT(C1),MATCH("*-S",INDEX(INDIRECT(C1),0,2),0),1)

I reformatted the page so C27 is now C1...

The formula is in cell H11, in H12, I need to get the 2nd occurrence of "*-S"

 

[Domenic]

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

Cell A2 is a helper cell. The formula in A2 returns a count made up of the number of occurrences. This number is then used by the formula in B2 and down. You can choose to enter these two formulas in two other cells. If you do, you'd need to change the reference ROWS(B$2:B2), accordingly.

 

[brooksc29]

Is there a way to do it without a count?

 

[brooksc29]

I am a little confused by the ROWS(B$2:B2) in the 2nd formula you gave me. I think b/c I want to refer to the range in INDIRECT(C1) to get my result, but maybe I'm not reading it right...

all I am looking for is the 2nd example of this in the same column of the range being searched... sorry to change C1 to A2 - had to reformat the page again...

=INDEX(INDIRECT(A2),MATCH("*-S",INDEX(INDIRECT(A2),0,2),0),1)

 

[Domenic]

If you're using Excel 2007 or later version, try the following instead...

=IFERROR(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),"")

And, if for example you enter the formula in G2, you'd need to replace...

ROWS(B$2:B2)

with

ROWS(G$2:G2)

 

[brooksc29]

the formula will be going in cell H13 and the cell that will reference the named range is in A2, so I adjusted the formula as follows...

=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(RIGHT(INDEX(INDIRECT($A$2),0,2),2)="-S",ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$13:H13)),1),"")

it's returning a value, but the incorrect value.

it is returning a cell that is located 1 row left and 3 cells below the 2nd version of "-s"

 

[brooksc29]

the only thing that seems to be changing the result is changing the ROW(H$13:H13), but I don't fully understand what I am changing and it's still not consistent.

 

[Domenic]

The formula searches the second column in the table referenced by the named range for entries that end in "-S", and returns the corresponding values from the first column of the table. Did you confirm the formula with CONTROL+SHIFT+ENTER, instead of just ENTER?

 

[brooksc29]

Thanks Dominic, I tried this formula as an array and it does return the next occurrence of "*-S".

{=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(RIGHT(INDEX(INDIRECT($A$2),0,2),2)="-S",ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$1:H2)),1),"")}

However, when I want the 3rd occurrence of "*-S" it's incorrect. I looks like it's counting cells down from the last occurrence instead of finding the next occurrence?

 

[Domenic]

If you start by entering the array formula in H1, you'll need replace...

ROWS(H$1:H2)

with

ROWS(H$1:H1)

However, if you start by entering the array formula in H2, you'll need to replace...

ROWS(H$1:H2)

with

ROWS(H$2:H2)

Does this help?