ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Pulling data from grid (https://www.excelbanter.com/excel-worksheet-functions/114122-pulling-data-grid.html)

MRR

Pulling data from grid
 
I have a grid with sales growth % listed across the grid on top and profit
growth listed vertically down the left side. I need to find the
intersection point between a particular sales growth number and profit growth
number (numbers are listed). Is there a formula (or other way ) that could
do this?

I.e. if sales growth is 3% and profit growth is 2%, I need the number 15
to appear in a cell


sales growth
1% 2% 3% 4% 5%
1% 5 10 15 20 30

2% 5 10 15 20 30

3% 5 10 15 20 30

4% 5 10 15 20 30

N Harkawat

Pulling data from grid
 
Say you data is in the range B3:F6 with row 2 used for Sales growth % from
b2:f2 and proft growth in cell A3:a6

this formula will get you 15

=INDEX($B$3:$F$6,MATCH(A4,$A$3:$A$6,0),MATCH(D2,$B $2:$F$2,0))
where above A4 = 2% and D2=3%



"MRR" wrote:

I have a grid with sales growth % listed across the grid on top and profit
growth listed vertically down the left side. I need to find the
intersection point between a particular sales growth number and profit growth
number (numbers are listed). Is there a formula (or other way ) that could
do this?

I.e. if sales growth is 3% and profit growth is 2%, I need the number 15
to appear in a cell


sales growth
1% 2% 3% 4% 5%
1% 5 10 15 20 30

2% 5 10 15 20 30

3% 5 10 15 20 30

4% 5 10 15 20 30


MRR

Pulling data from grid
 
What if the data doesn't "Match" but rather is in a range. Is there another
word to use?

"N Harkawat" wrote:

Say you data is in the range B3:F6 with row 2 used for Sales growth % from
b2:f2 and proft growth in cell A3:a6

this formula will get you 15

=INDEX($B$3:$F$6,MATCH(A4,$A$3:$A$6,0),MATCH(D2,$B $2:$F$2,0))
where above A4 = 2% and D2=3%



"MRR" wrote:

I have a grid with sales growth % listed across the grid on top and profit
growth listed vertically down the left side. I need to find the
intersection point between a particular sales growth number and profit growth
number (numbers are listed). Is there a formula (or other way ) that could
do this?

I.e. if sales growth is 3% and profit growth is 2%, I need the number 15
to appear in a cell


sales growth
1% 2% 3% 4% 5%
1% 5 10 15 20 30

2% 5 10 15 20 30

3% 5 10 15 20 30

4% 5 10 15 20 30


MRR

Pulling data from grid
 
Disregard that last replay, I figured it out. You use "1" instead of "0" in
the Match formula.

Thanks for your help N Harkawat!!!

"MRR" wrote:

What if the data doesn't "Match" but rather is in a range. Is there another
word to use?

"N Harkawat" wrote:

Say you data is in the range B3:F6 with row 2 used for Sales growth % from
b2:f2 and proft growth in cell A3:a6

this formula will get you 15

=INDEX($B$3:$F$6,MATCH(A4,$A$3:$A$6,0),MATCH(D2,$B $2:$F$2,0))
where above A4 = 2% and D2=3%



"MRR" wrote:

I have a grid with sales growth % listed across the grid on top and profit
growth listed vertically down the left side. I need to find the
intersection point between a particular sales growth number and profit growth
number (numbers are listed). Is there a formula (or other way ) that could
do this?

I.e. if sales growth is 3% and profit growth is 2%, I need the number 15
to appear in a cell


sales growth
1% 2% 3% 4% 5%
1% 5 10 15 20 30

2% 5 10 15 20 30

3% 5 10 15 20 30

4% 5 10 15 20 30



All times are GMT +1. The time now is 12:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com