3 variable lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rooter - 09 May 2008 22:18 GMT

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

Reply to this Message

T. Valko - 09 May 2008 23:08 GMT

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

Signature

Biff
Microsoft Excel MVP

> I'm newly impressed with what Excel can allow me to do, but am running
> into
[quoted text clipped - 15 lines]
> variable of company name...maybe I'm just not seeing a simple way to do
> it...?

Reply to this Message

rooter - 10 May 2008 02:04 GMT

Wow! Thanks...much appreciated.

> Let's assume:
>
[quoted text clipped - 26 lines]
> > variable of company name...maybe I'm just not seeing a simple way to do
> > it...?

Reply to this Message

rooter - 10 May 2008 02:58 GMT

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!

> Let's assume:
>
[quoted text clipped - 26 lines]
> > variable of company name...maybe I'm just not seeing a simple way to do
> > it...?

Reply to this Message

T. Valko - 10 May 2008 04:22 GMT

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

Signature

Biff
Microsoft Excel MVP

> Actually, I have a follow up. What you suggested worked charmingly and I
> am
[quoted text clipped - 51 lines]
>> > variable of company name...maybe I'm just not seeing a simple way to do
>> > it...?

Reply to this Message

rooter - 10 May 2008 21:33 GMT

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

> Here are 2 ways:
>
[quoted text clipped - 78 lines]
> >> > variable of company name...maybe I'm just not seeing a simple way to do
> >> > it...?

Reply to this Message

T. Valko - 10 May 2008 22:19 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Thank you again...very instructive and very helpful. I'm a fan!
>
[quoted text clipped - 89 lines]
>> >> > do
>> >> > it...?

Reply to this Message