look up on multiple sheets
 

i have a workbook made up of about 50 worksheets with same column headers but
different row counts.

In sheet one is a handfull of user names that could be found on any of the
50 sheets.

how do i adjust this array so that it searches through all 50 sheets instead
of the 3 columns currently identified in this formula?
=MAX(IF((A$2:A$20212=D11)*(B$2:B$20212<=TODAY()),B $2:B$20212))