3 variable lookup

[rooter]

I'm newly impressed with what Excel can allow me to do, but am running into
an issue which may or maynot be doable...so please help if you can:

I want to find the sales number for a company by year in a sheet that is
organized as follows:

Column 1 starting row3: Sales
Row 1 starting column 2: Company Name
Row 3 starting column2: Year

company A A A B B B
Year 2007 2006 2005 2007 2006 2005
sales 1.20 2.29 2.17 1.14 3.75 3.57
assets 0.90 1.06 1.06 0.47 1.67 1.46

hlookup only works if I want to find sales by year but the additional
variable of company name...maybe I'm just not seeing a simple way to do it...?

 

[T. Valko]

Let's assume:

Company names = B1:G1
Year = B2:G2
Sales = B3:G3

A1 = some company name
A2 = some year number

=INDEX(B3:G3,MATCH(1,INDEX((B1:G1=A1)*(B2:G2=A2),0),0))

 

[rooter]

Wow! Thanks...much appreciated.

Let's assume:

Company names = B1:G1
Year = B2:G2
Sales = B3:G3

A1 = some company name
A2 = some year number

=INDEX(B3:G3,MATCH(1,INDEX((B1:G1=A1)*(B2:G2=A2),0),0))

 

[rooter]

Actually, I have a follow up. What you suggested worked charmingly and I am
thinking about perhaps a more efficient way if there is one.

In the solution thus far, I need to change the row every time I need to look
for a new variable. For intance, when I want "Sales" I specify the row that
has sales figures once I have indexed the column in which I need to be
looking. But when I want "Assets" I have to redefine the new row that had the
data for assets.

So, the question is, can I define the lookup in the entire data range that
has both rows -- data for sales and for assets. That is, is there a hlookup
function that can be nested within the Index function to cut down the need to
define a new row everytime I need a particular variable? Can I tell the
cursor to move to row 3 to pick up sales and to move to row 4 to pick up
assets?

Thanks!

 

[T. Valko]

Here are 2 ways:

A1:A4 = row headers = Company, Year, Sales, Assets
B1:G1 = company names
B2:G2 = year numbers
B3:G3 = sales numbers
B4:G4 = asset numbers

A9 = some company name
A10 = some year number
A11 = Sales or Assets

=INDEX(B3:G4,MATCH(A11,A3:A4,0),MATCH(1,INDEX((B1:G1=A9)*(B2:G2=A10),0),0))

Or......

B1:G1 = defined named range = company
B2:G2 = defined named range = year
B3:G3 = defined named range = sales
B4:G4 = defined named range = assets

A9 = some company name
A10 = some year number
A11 = Sales or Assets

=SUMPRODUCT(--(Company=A9),--(Year=A10),INDIRECT(A11))

 

[rooter]

Thank you again...very instructive and very helpful. I'm a fan!

 

[T. Valko]

You're welcome. Thanks for the feedback!