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...?

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))

--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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...?

Wow! Thanks...much appreciated.

"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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...?

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" wrote:

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))

--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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...?

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))


--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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" wrote:

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))

--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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...?

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

"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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" wrote:

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))

--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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...?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
Thank you again...very instructive and very helpful. I'm a fan!

"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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" wrote:

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))

--
Biff
Microsoft Excel MVP


"rooter" wrote in message
...
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...?