INDEX using INDIRECT with OR function

brooksc29

Active Member
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?

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Drrellik

Well-known Member
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

MrExcel MVP
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

Active Member
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

MrExcel MVP
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

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

brooksc29

Active Member
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

Active Member
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

MrExcel MVP
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

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

Replies
4
Views
169
Replies
2
Views
483
Replies
9
Views
571
Replies
3
Views
225
Replies
1
Views
386

1,195,923
Messages
6,012,314
Members
441,690
Latest member
CyberWrek

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back