Challenging Formula. Need help with writing a formula

[tom]

I have a formula that I'm trying to write and I feel that I'm in a nightmare.

Maybe someone can shed some light for me on this.

EXCELL SHEET 1

Column Column Column
A B
C

Sales & GP % Sales Growth % GP $ Growth

Row 1 TRS #DIV/0! #DIV/0!
Row 2 TRP #DIV/0! #DIV/0!
Row 3
#DIV/0!


Potential % Earned Incentive
$400
Earned

Row 6 $200 (x) (x)
Row 7 $120 (x) (x)
Row 8 $80 (x) (x)



EXCELL SHEET 2

Chart or Grid

Column Column Column Column Column
Column
A B C D
E F

% G.P $ GROWTH

Row 1 19.0% 20.0% 21.0% 22.0%
23.0%

% Row 2 19.0% 80% 90% 100% 105%
110%

S Row 3 20.0% 90% 100% 105% 110%
110%
A
L Row 4 21.0% 100% 105% 110% 110%
110%
E
S Row 5 22.0% 105% 110% 110% 110%
110%

G Row 6 23.0% 110% 110% 110% 110%
110%
R
O
W
T
H

Goal:
I need to calculate and find (x) above.

Problem:

Excel Sheet 1
Cell's: B1; C1; B2; C2; C3 are the result of a circular reference.

Depending on what the calculated % result in Excel sheet 1 cell B1; C1; B2;
C2; C3, determines where to find the percentage on the Grid or Chart in Excel
sheet 2

For Example:

The calc. % in Excel sheet 1 cell: B1 = 21.8%
and
The Calc % in Excel sheet 1 cell: C1 = 23.9%

I need excel to use the calculated results. (found in Excel Sheet 1 Cell:B1;
C1)
Then, use these calc %'s to find the correct % from grid on Excel sheet 2
(it should fall somewhere between Columns A - F and Rows 1 - 6 in excel sheet
2)
Then ultimately return the result (%) : (in this ex. 110%) to Excel
Sheet 1 Cell: B6

I need to repeat the formula to find the % and return the result to Excel
Sheet 1 Cell: B7, B8.

The rest is history from there. I can write the formula that multiplies
Excel sheet 1 Cell: B6*A6

If anyone is willing to take on this challenge and help me with the formula.
I would appreciate the help.

Thanks

 

[Joel]

Try something like this

=INDEX(Sheet2!A1:F6,MATCH(B1,Sheet2!B1:F1,0),MATCH(C1,Sheet2!A2:A6,0))

 

[tom]

Thanks for your help. It's getting me closer. I still can't get it to find
the number in the grid. But I'm much further than were I was.

 

[tom]

I got it to work.

Joel, thank you very Much!!