Index with multiple matches from seperate tabs

[mtaylor]

Hi all,

Ok, I have the student login details for everyone in the school. I've created this dashboard but need it a little more secure than simply typing in the orison number (unique student identifier), I want the use to type in their orison number and their DOB.

Where the class (KG2A) is in the picture below, I am using the formula: =index(UPS!B2:B,(match(F6,UPS!S2:S,0))*(match(C5,UPS!C2:C,0)))

UPS is the seperate tab which holds all the information required.
Column B = class
Column C = orison number
Column S = DOB

My issue atm, is that it worked, however when I input change the DOB but keep the orison number as is, the class remains the same. I want the two variables to work together and produce the class, and if they don't work together produce an error.

Many thanks

 

[jasonb75]

Try this one which must be entered as an array formula. In excel that is done by pressing Ctrl Shift and Enter together, it appears that you are using google sheets which may be slightly different.

=index(UPS!B:B,SMALL(IFERROR(ROW(UPS!B2:B)/(F6=UPS!S2:S)/(C5=UPS!C2:C),""),1))

 

[mtaylor]

Try this one which must be entered as an array formula. In excel that is done by pressing Ctrl Shift and Enter together, it appears that you are using google sheets which may be slightly different.

=index(UPS!B:B,SMALL(IFERROR(ROW(UPS!B2:B)/(F6=UPS!S2:S)/(C5=UPS!C2:C),""),1))

Love it!!

Many thanks.