Changing named range reference depending on a cell's content

[cparaske]

Where to start?!

I've got the following formula pulling data in from a second spreadsheet within the same workbook:

=IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0)

We have 8 different locations ("MICH" being one of them) that we need to be able to access. I can write a nested IF formula that looks at cell I7 (that contains a list of all 8 locations) and, depending on I7's content, brings back the desired values.

I was hoping someone in the forum could help me write a simpler formula that would not have 7 IF statements embedded in it.

Any help will be greatly appreciated.

Thanks,
Cristo