cross reference two
 

I'm need to write a formula based on information in a table - as an example:

High Performing Location Market Area Non-Market Area
New Location
1 $1,000,000 $750,000 $500,000
$200,000
2 800,000 600,000 300,000
100,000
3 550,000 400,000 200,000
50,000

I want to lookup the information in the top row and on the left column, then
multiply by a factor referenced in another cell. I have tried If,then with
vlookups and that's not working for me. What's the easiest way to accomplish
this?

I appreciate the help.

auntieb

cross reference two
 

If I understand what you're looking for,
with headers in Row1,
and data in A2 to D4,
enter the value from Column A into say, E1,
and the column header label in E2,
and this formula should return the cross referenced value:

=SUMPRODUCT((A2:A4=E1)*(B1:D1=E2)*B2:D4)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"auntieb" wrote in message
...
I'm need to write a formula based on information in a table - as an
example:

High Performing Location Market Area Non-Market Area
New Location
1 $1,000,000 $750,000 $500,000
$200,000
2 800,000 600,000
300,000
100,000
3 550,000 400,000
200,000
50,000

I want to lookup the information in the top row and on the left column,
then
multiply by a factor referenced in another cell. I have tried If,then
with
vlookups and that's not working for me. What's the easiest way to
accomplish
this?

I appreciate the help.

auntieb

cross reference two
 

thanks for the feedback Ragdyer. I ended up using the following to get what
I needed before I got your response.

=VLOOKUP($E5,'Production & Market Tables'!$B$5:$G$7,MATCH($C$5,'Production &
Market Tables'!$B$4:$G$4,0),0)*'FSO Assignment Summary'!F5

Thanks again!

"Ragdyer" wrote:

If I understand what you're looking for,
with headers in Row1,
and data in A2 to D4,
enter the value from Column A into say, E1,
and the column header label in E2,
and this formula should return the cross referenced value:

=SUMPRODUCT((A2:A4=E1)*(B1:D1=E2)*B2:D4)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"auntieb" wrote in message
...
I'm need to write a formula based on information in a table - as an
example:

High Performing Location Market Area Non-Market Area
New Location
1 $1,000,000 $750,000 $500,000
$200,000
2 800,000 600,000
300,000
100,000
3 550,000 400,000
200,000
50,000

I want to lookup the information in the top row and on the left column,
then
multiply by a factor referenced in another cell. I have tried If,then
with
vlookups and that's not working for me. What's the easiest way to
accomplish
this?

I appreciate the help.

auntieb

cross reference two
 

Thanks for feeding back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"auntieb" wrote in message
...
thanks for the feedback Ragdyer. I ended up using the following to get what
I needed before I got your response.

=VLOOKUP($E5,'Production & Market Tables'!$B$5:$G$7,MATCH($C$5,'Production &
Market Tables'!$B$4:$G$4,0),0)*'FSO Assignment Summary'!F5

Thanks again!

"Ragdyer" wrote:

If I understand what you're looking for,
with headers in Row1,
and data in A2 to D4,
enter the value from Column A into say, E1,
and the column header label in E2,
and this formula should return the cross referenced value:

=SUMPRODUCT((A2:A4=E1)*(B1:D1=E2)*B2:D4)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"auntieb" wrote in message
...
I'm need to write a formula based on information in a table - as an
example:

High Performing Location Market Area Non-Market Area
New Location
1 $1,000,000 $750,000
$500,000
$200,000
2 800,000 600,000
300,000
100,000
3 550,000 400,000
200,000
50,000

I want to lookup the information in the top row and on the left column,
then
multiply by a factor referenced in another cell. I have tried If,then
with
vlookups and that's not working for me. What's the easiest way to
accomplish
this?

I appreciate the help.

auntieb