See attached for a possible solution.
I added a helper column in Sheet2, column J
In J2, add formula:
=IF(AND(OR(EVERYTHING!$A$2=A2,EVERYTHING!$A$2=""),OR(EVERYTHING!$A$5=C2,EVERYTHING!$A$5=""),OR(EVERYTHING!$A$11=D2,EVERYTHING!$A$11=""),OR(EVERYTHING!$A$8=E2,EVERYTHING!$A$8=""),OR(EVERYTHING!$A$14=G2,EVERYTHING!$A$14=""),OR(EVERYTHING!$A$17=H2,EVERYTHING!$A$17="")),MAX($I$1:I1)+1,0)
and copy down.
in Sheet 'EVERYTHING', A20 add formula:
=IF(A2&A5&A8&A11&A14&A17="",0,MAX(Sheet2!I:I))
Then in C2 add formula:
=IF(ROWS($A$2:$A2)>$A$20,"",INDEX(Sheet2!D$2:D$375,MATCH(ROWS($A$2:$A2),Sheet2!$I$2:$I$375,0)))
and copy down.
Use similar formula in D2:J2 and adjust the INDEX range to suit...
Then copy all down.
You can hide A20 by making font colour white.
You can hide column J on Sheet2 if desired.
You should be able to make any entries in the input boxes and matches should come up.. even if you leave some inputs blank.
Bookmarks