Pulling "Matrix" Data
 

I am trying to return information using look-ups by looking up info that is
in a column and a row....so

Region 1 Region 2 Region 3

Sales Revenue Sales Revenue Sales Revenue
Code 1
Code 2
Code 3

So, say I want the revenue for Code 1 and Region 1 returned in a
report....I'm just not sure of the syntax....do I create a name reference and
do a look-up from that? Something like lookup(Code1|Region1,Cell
References,2,False)?

Pulling "Matrix" Data
 

Matt,
Don't know the source of your data, but have you looked into Pivot Tables ?

NickHK

"Matt" ...
I am trying to return information using look-ups by looking up info that is
in a column and a row....so

Region 1 Region 2 Region 3

Sales Revenue Sales Revenue Sales Revenue
Code 1
Code 2
Code 3

So, say I want the revenue for Code 1 and Region 1 returned in a
report....I'm just not sure of the syntax....do I create a name reference
and
do a look-up from that? Something like lookup(Code1|Region1,Cell
References,2,False)?

Pulling "Matrix" Data
 

Assume M1 holds the string "Region1"
M2 holds the string "code 2"

further assume the Region Name is in the Sales column in each case

Match(M1,A1:G1,0) give the column for region 1
Match(M1,A1:G1,0)+1 gives the Revenue column for Region 1

Match(M2,A1:A100,0) gives the row for code 2

Put those in the index function
=Index(A1:G100,Match(M2,A1:A100,0),Match(M1,A1:G1, 0)+1)

gives the revenue for the appropriate code.

--
Regards,
Tom Ogilvy


"Matt" wrote:

I am trying to return information using look-ups by looking up info that is
in a column and a row....so

Region 1 Region 2 Region 3

Sales Revenue Sales Revenue Sales Revenue
Code 1
Code 2
Code 3

So, say I want the revenue for Code 1 and Region 1 returned in a
report....I'm just not sure of the syntax....do I create a name reference and
do a look-up from that? Something like lookup(Code1|Region1,Cell
References,2,False)?

Pulling "Matrix" Data
 

Hello Nick - thanks for the response....I'm actually summarizing in a pivot
table...I want to then pull that information into a report that is
dynamic....i.e. the user has the abilility to get the report for all codes,
all regions, specific regions, etc....

"NickHK" wrote:

Matt,
Don't know the source of your data, but have you looked into Pivot Tables ?

NickHK

"Matt" ...
I am trying to return information using look-ups by looking up info that is
in a column and a row....so

Region 1 Region 2 Region 3

Sales Revenue Sales Revenue Sales Revenue
Code 1
Code 2
Code 3

So, say I want the revenue for Code 1 and Region 1 returned in a
report....I'm just not sure of the syntax....do I create a name reference
and
do a look-up from that? Something like lookup(Code1|Region1,Cell
References,2,False)?

Pulling "Matrix" Data
 

Thank you Tom!

"Tom Ogilvy" wrote:

Assume M1 holds the string "Region1"
M2 holds the string "code 2"

further assume the Region Name is in the Sales column in each case

Match(M1,A1:G1,0) give the column for region 1
Match(M1,A1:G1,0)+1 gives the Revenue column for Region 1

Match(M2,A1:A100,0) gives the row for code 2

Put those in the index function
=Index(A1:G100,Match(M2,A1:A100,0),Match(M1,A1:G1, 0)+1)

gives the revenue for the appropriate code.

--
Regards,
Tom Ogilvy


"Matt" wrote:

I am trying to return information using look-ups by looking up info that is
in a column and a row....so

Region 1 Region 2 Region 3

Sales Revenue Sales Revenue Sales Revenue
Code 1
Code 2
Code 3

So, say I want the revenue for Code 1 and Region 1 returned in a
report....I'm just not sure of the syntax....do I create a name reference and
do a look-up from that? Something like lookup(Code1|Region1,Cell
References,2,False)?

Pulling "Matrix" Data
 

Matt wrote:
I am trying to return information using look-ups by looking up info that is
in a column and a row....so

Region 1 Region 2 Region 3

Sales Revenue Sales Revenue Sales Revenue
Code 1
Code 2
Code 3

So, say I want the revenue for Code 1 and Region 1 returned in a
report....I'm just not sure of the syntax....do I create a name reference and
do a look-up from that? Something like lookup(Code1|Region1,Cell
References,2,False)?

I would recommend the following kind of matrix:

Sales1 Revenue1 Sales2 Revenue2 Sales3 Revenue3
Code1 amtA amtB amtC amtD amtE amtF
Code2 amtG amtH amtI amtJ amtK amtL
Code3 amtM amtN amtO amtP amtQ amtR

Then highlight the tabLE including column and row headings, click
Insert|Name|Create, check Top row and Left column, click OK

Then =Code1 Sales1 will return amtA, =Code2 Revenue3 will return amtL

And =Code3 Sales3&", "&Revenue3 will return amtQ, amtR

Alan Beban