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]

it seems to be working... one more test... what if there are multiple criteria? Could I ask to find the 2nd occurrence of a few different things? Could it also find the 2nd occurrence of text w/in a cell? Like if I entered "*CF*" it would give me the 2nd time it found CF in any place within the cell?

right now I have a formula that works with multiple criteria in this form, but can't get it to work for the 2nd occurrence
{=INDEX(INDIRECT(A2),MATCH(TRUE,ISNUMBER(MATCH(INDEX(INDIRECT(A2),0,2),{"CF","LF","RF","OF"},0)),0),1)}

 

[brooksc29]

i think i have it working now... what if the conditions are set on different criteria? would I be able to ask find the 2nd occurrence of any of the following? like with this formula

{=INDEX(INDIRECT(A2),MATCH(TRUE,ISNUMBER(MATCH(INDEX(INDIRECT(A2),0,2),{"CF","LF","RF","OF"},0)),0),1)}

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(ISNUMBER(MATCH(INDEX(INDIRECT($A$2),0,2),{"CF","LF","RF","OF"},0)),ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$2:H2)),1),"")

 

[brooksc29]

Thank you Dominic! That works well.

If I have multiple criteria, can I also search for text w/in a cell? Would something like this work?
{=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(ISNUMBER(MATCH(INDEX(INDIRECT($A$2),0,2),{"3B","3B-*"},0)),ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$1:H2)),1),"")}

 

[brooksc29]

that formula isn't working and I think why is because what I am really asking is - return the 2nd occurrence of "3B" or the 1st occurrence of "3b-*", which ever comes first in the column...

is it possible to write it like that?

 

[brooksc29]

it's basically combining

Find the 2nd occurrence of "3B"
{=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(ISNUMBER(MATCH(INDEX(INDIRECT($A$2),0,2),{"3B"},0)),ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$1:H2)),1),"")}

with an OR function

Find the 1st occurrence of "3B-*"
=IFERROR(INDEX(INDIRECT(A2),MATCH("3B-*",INDEX(INDIRECT(A2),0,2),0),1), "NONE")

which ever comes first.

I am also finding out that I am having repeats on the sheet. It's because I have to search for the either or. As a safety net, is there anyway to set a precondition that the result that is returned in the cell can only appear once on the entire sheet? And if the result is a duplicate, automatically return the next occurrence of the set conditions?

I realize this is a lot of conditions and a very long formula. I can definitely email you the workbook if needed.

Thank you again in advance Dominic! You are an amazing advisor!

 

[brooksc29]

came up with this, not getting a result to return

{=IFERROR(OR(INDEX(INDIRECT($A$2),SMALL(IF(LEFT(INDEX(INDIRECT($A$2),0,2),2)="3B",ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$1:H2)),1),INDEX(INDIRECT(A2),MATCH("3B-*",INDEX(INDIRECT(A2),0,2),0),1)),"")}

 

[brooksc29]

I think I may have found another bug.

=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(ISNUMBER(MATCH(INDEX(INDIRECT($A$2),0,2),{"CF","LF","RF","OF"},0)),ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$1:H2)),1),"")

is returning the 2nd occurrence of {"CF","LF","RF","OF"}, so if it sees one CF, then one LF, it returns the first LF it sees.

What I really need is return the 2nd occurrence of CF or LF or RF or OF, which ever comes first. So if it sees CF, then LF, then RF, then OF, then OF again, return the 2nd OF.

or if it sees CF then LF then RF then CF, return the 2nd CF.

 

[Domenic]

I think I may have found another bug.

=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(ISNUMBER(MATCH(INDEX(INDIRECT($A$2),0,2),{"CF","LF","RF","OF"},0)),ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$1:H2)),1),"")

is returning the 2nd occurrence of {"CF","LF","RF","OF"}, so if it sees one CF, then one LF, it returns the first LF it sees.

What I really need is return the 2nd occurrence of CF or LF or RF or OF, which ever comes first. So if it sees CF, then LF, then RF, then OF, then OF again, return the 2nd OF.

or if it sees CF then LF then RF then CF, return the 2nd CF.

The reason you're getting incorrect results is that you're still incorrectly referencing the range for the ROWS function. 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)

The ROWS function counts the number of rows within a range reference. In this formula, we're using it to return a series of numbers (ie. 1, 2, 3, etc.). So if you start by entering the array formula in H2, we would have ROWS(H$2:H2), which returns 1. When the formula is copied to the next row below, the range reference changes and we would have ROWS(H$2:H3), which returns 2, and so on.

 

[brooksc29]

so in G2 I have LF and in H2 it says =IFERROR(INDEX(INDIRECT(A2),MATCH("LF*",INDEX(INDIRECT(A2),0,2),0),1), "NONE") - find me the first LF

in G3 its CF and H3 is =IFERROR(INDEX(INDIRECT(A2),MATCH("CF*",INDEX(INDIRECT(A2),0,2),0),1), "NONE") - find me the first CF

in G4 its RF and H4 is =IFERROR(INDEX(INDIRECT(A2),MATCH("RF*",INDEX(INDIRECT(A2),0,2),0),1),"NONE") - find me the first RF

in G5 it's OF and H5 {=IFERROR(INDEX(INDIRECT($A$2),SMALL(IF(ISNUMBER(MATCH(INDEX(INDIRECT($A$2),0,2),{"CF","LF","RF","OF"},0)),ROW(INDEX(INDIRECT($A$2),0,2))-MIN(ROW(INDEX(INDIRECT($A$2),0,2)))+1),ROWS(H$1:H4)),1),"")}

what I need H5 to say is, find either the 2nd instance of LF, CF, RF or the first instance of OF, LF-*, CF-*, RF-*

I don't think it's saying that right now...