Using IF(Search(cell, cell), INDEX(range, MATCH(cell, range, 0))

[lizmaryray]

Hi,
I need to search multiple sheets on excel and bring the data into a single sheet.

I have 10+ sheets like the example below.

[TABLE="width: 1027"]
<colgroup><col span="2"><col><col span="5"><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Tracy[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Reference No.[/TD]
[TD]Client[/TD]
[TD]Review Area[/TD]
[TD]% Done[/TD]
[TD]Loan Count[/TD]
[TD]Total Loans Completed[/TD]
[TD]Average Min Per Loan[/TD]
[TD]Number of Issues[/TD]
[TD]Start Date[/TD]
[TD]Analyst Deadline[/TD]
[TD]Analyst Complete[/TD]
[TD]Notes: Need something to finish[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AFR[/TD]
[TD]New Loans[/TD]
[TD]0%[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AFR[/TD]
[TD]Complaints[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]-[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AFR[/TD]
[TD]General[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]-[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AFR[/TD]
[TD]Escrow[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]-[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AFR[/TD]
[TD]Loss Drafts[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]-[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AFR[/TD]
[TD]Paid in Full[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]-[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AFR[/TD]
[TD]Collections[/TD]
[TD]0%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]-[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Then I have another sheet showing this view

[TABLE="width: 799"]
<colgroup><col><col><col span="3"><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Report Due[/TD]
[TD] [/TD]
[TD]Report Sent[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]AFR[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]% Done[/TD]
[TD]Loan Count[/TD]
[TD]Total Complete[/TD]
[TD]Number of Issues[/TD]
[TD]Average Min Per Loan[/TD]
[TD]Analyst[/TD]
[TD]Analyst Deadline [/TD]
[TD]Analyst Complete[/TD]
[TD]Notes and:
IF Deadline missed explain why[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1/0/1900[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


As I fill in the above sheet with an analyst name it is connected to one of the sheets exampled at the top. If I already know the analyst name I can use this formula successfully
=IF(SEARCH($J6, TM!$J$2), INDEX(Table430[% Done], MATCH($D5, Table430[Reference No.], 0)))

However, I really want to be able to just stick a name in there and have the formula work automatically and search each sheet without having to know who the analyst will be. Which is why I was originally using the search function. Also the top sheets are also tables that can be filtered, so I can have it connect to a certain row.

Can anyone help?? Thanks!