Multiple use of lookup - Tough one?

[drwidder]

I consider myself reasonably adept at Excel but can't figure this out
even after reading related posts on this forum.

I am trying to find an elegant way to do this:

I have data for estimated single store sales, example:

Month of Operation After opening: 0 1 2 3 4 5 6
Sales$: _____________________
10 20 30 40 50 60 70

I want to project sales for multiple stores, opened in differen
months, based on the above data. For example, I have:

Store _____ Month Opened
Store A _____ 1
Store B _____ 2
Store C _____ 3
Store D _____ 3

And I want to find:
Total Sales, all stores, by month based on this data.

It would be easy if HLOOKUP could look up more than one index at
time. Simple example attached.

Thank you in advance for help on a frustrating problem.

-DR

 

[Bernard Liengme]

Let the table with
Month 1 2 3...
Sales 10 20 30...
be in A1:k2

Let the month for store 1 be in E5
For a horizontal lookup use =HLOOKUP(E5,B1:K2,2)

or, since the first row of the table is the sequence 1,2,3,; use
=INDEX(B2:K2,1,E5)

 

[drwidder]

Bernard
Thanks for the help, but I am trying to get a formula to compute tha
total sales (store A, B, C, and D) at once. I would like to sa
something like SUM( HLOOKUP( K5:K8, A2:Y3,2))
but HLOOKUP won't look up multiple values like that