VLOOKUP - Across Multiple Sheets

[idontexcelinexcel]

I'm having trouble with a VLOOKUP and need some help.
We have customers across various states and each state is further split up into various regions.
I have a sheet for each state, with its regions and its customers.

I want to create a VLOOKUP on the summary page, that will look at the respective state and return the result for that particular customer, region and month.

See attached for clarification.

Thanks!

[Jdevil]

Can there be more than one region for a customer? If not, in cell E4 and drag down:
=INDEX(INDIRECT("'"&B4&"'!$F$3:$F$10"),MATCH(C4,INDIRECT("'"&B4&"'!$B$3:$B$10"),0))

[Pete_UK]

You can use this formula in E4:

=VLOOKUP(C4,INDIRECT("'"&B4&"'!B3:O10"),MATCH(E$3,INDIRECT("'"&B4&"'!B2:O2"),0),0)

then copy down.

Hope this helps.

Pete

[azumi]

another version

=LOOKUP(2,1/(INDIRECT("'"&$B4&"'!B3:B10")=$C4),INDEX(INDIRECT("'"&$B4&"'!D3:O10"),0,MATCH($E$3,INDIRECT("'"&$B4&"'!D2:O2"),0)))

[idontexcelinexcel]

Hey Guys,

Thanks so much for the help.
Is it possible to add another dimension to the formula? In other words, can it also look for a match on the region as well?

Thanx!

[Jdevil]

Hi, try this:

=INDEX(INDIRECT("'"&B4&"'!$F$3:$F$10"),MATCH(C4&D4,INDIRECT("'"&B4&"'!$B$3:$B$10")&INDIRECT("'"&B4&"'!$C$3:$C$10"),0))

You have to enter it by pressing CTRL + SHIFT + ENTER

[idontexcelinexcel]

Thanks Guys! They all worked beautifully!