Return index value position for value based on two criteria

[jeffreybrown]

I'm helping somebody with a spreadsheet formula and hoping I can get some further help myself.

I'm using a formula succesfully, but only with one criteria. I need to add a second criteria, but not sure how to fit it in.

CSE (Cell A4 and copied down)
=IF(ROWS($A$4:A4)<=$A$2,SMALL(IF(ssclass=A$1,ROW(ssclass)-ROW($A$2)+1),ROWS($A$4:A4)),"")

Right now it's looking at the named range ssclass and comparing against A1 but I also need it to compare against a named range routenum and match against A3.

So basically, the formula should look at A1 (Infant) and also A3 (Rt # 1) and return the index position so B4 and C4 can return the first and last names which works fine. Just not A4.

Any thoughts?

[daddylonglegs]

Hello Jeff, try this formula in A4 confirmed with CTRL+SHIFT+ENTER and copied down

=IF(ROWS($A$4:A4)<=$A$2,SMALL(IF((ssclass=A$1)*(routenum=A$3),ROW(ssclass)-MIN(ROW(ssclass))+1),ROWS($A$4:A4)),"")

I highlighted changes/additions in red

[jeffreybrown]

Hi DLL,

Amazing as always...Thanks a million