making a lookup formula more concise

**ammartino44** · 02-21-2014, 07:57 PM

Hello. Is there any way that I can improve this formula? There are two lookups HOUSE DIVISION and NATIONAL DIVISION and everything else is the same.

=INDEX(INDIRECT("'["&$B$11&".xls]Sheet1'!$E$77:$E$851"),MATCH("HOUSE DIVISION",INDIRECT("'["&$B$11&".xls]Sheet1'!$B$77:$B$851"),0))+INDEX(INDIRECT("'["&$B$11&".xls]Sheet1'!$E$77:$E$851"),MATCH("NATIONAL DIVISION",INDIRECT("'["&$B$11&".xls]Sheet1'!$B$77:$B$851"),0))

**TMS** · 02-21-2014, 08:36 PM

My guess is no because the two MATCHes will return different row numbers; one for HOME DIVISION and another for NATIONAL DIVISION. Then you return the matching values from column B and add them together.

Regards, TMS

**TMS** · 02-21-2014, 08:40 PM

Thinking about it, you could probably build this in a Helper Cell:

Formula:

Please Login or Register  to view this content.

Then refer to that Helper Cell in each of your INDIRECT statements. That'd be shorter.

Regards, TMS

**shg** · 02-21-2014, 08:40 PM

Maybe something like

=sum(INDEX(INDIRECT("'["&$B$11&".xls]Sheet1'!$E$77:$E$851"), MATCH({"HOUSE DIVISION","NATIONAL DIVISION"}, INDIRECT("'["&$B$11&".xls]Sheet1'!$B$77:$B$851"), 0)))

More compact, but no more efficient. The INDIRECT makes it volatile, and the exact match makes it brutal.