Lookup across multiple sheets in a workbook

[ysplace]

Good Morning,

I have a question for the gurus. Each month, I have to pull a report of the number of closed loans. So, I have a work book with 24 sheets, 12 month from one system and 12 from another system. What I have found is that the date staff are using to mark as closed seems to change. So some loans that originally were in my Jan report is now on my feb report. I pulled a fresh list running Jan to August. What I want is to do some type of lookup that will look at sheet 1 column B loan number then search column I on sheets 2-25 then return to sheet 1 column A the sheet name its found on. Hopefully this makes sense. Is this possible?

Thank you

 

[Domenic]

Assuming that the loan numbers in Column B start at the second row, and that your sheet names are in fact named 2, 3, 4, 5, etc...

A2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX(ROW(INDIRECT("2:25")),MATCH(TRUE,COUNTIF(INDIRECT("'"&ROW(INDIRECT("2:25"))&"'!I:I"),B2)>0,0))

Hope this helps!