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