database - Getting Excel to return a row which has two values -

database - Getting Excel to return a row which has two values -

i have csv file has fixtures of football game league between 2003 , 2012 (this in sheet1). want excel homecoming row containing fixtures worksheet, i.e. when team (in cell $b$18) played team b (in cell $d$18) home , away. have used array formula:

=index(sheet1!$b:$f,small(if(or(and(sheet1!$c:$c=$b$18,sheet1!$d:$d=$d$18),and(sheet1!$c:$c=$d$18,sheet1!$d:$d=$b$18)),row(sheet1!2:2)),2))

where sheet 1 has

column b: date, column c: home team, column d: away team, column e: home score, column f: away score

unfortunately doesn't seem work. can point out error in formula?

new answer

i assume familiar array formulas, looks using.

i have managed formula work me entered array formula

=iferror(index(sheet1!$b$2:$f$10,small(if(((sheet1!$c$2:$c$10=$b$18)*(sheet1!$d$2:$d$10=$d$18))+((sheet1!$c$2:$c$10=$d$18)*(sheet1!$d$2:$d$10=$b$18)),row(sheet1!$c$2:$c$10)-1),row(a1)),f$17),"")

where f$17 refers column homecoming ie 2 home team, 3 away. have used reference can re-create across 4 columns , home , away teams , scores. copied downwards many rows expect returned.

database arrays excel lookup