COMPLEX "if" formula

[mikevalsoft]

	< 2%	2 to 5%	5+ to 8%	8+ to 11%	11+%
0 to 10%	0.6	0.8	1	1	1.25
10+ to 15%	0.8	1	1	1.25	1.35
15+ to 25%	1	1.25	1.25	1.35	1.5
25+ to 35%	1.25	1.35	1.5	2	2.25

<tbody>
</tbody>

Column growth: X
Row growth: Y
Multiplier: Z

Given this table, I would like to write a formula that will equal to the number in the table. In other words, if I have 3.5% growth in the column section and 18% growth in the row section, then the number generate by the formula is equal to 1.25.

The purpose of this formula is to have someone change X and/or Y and be able to generate the multiplier in the table. As per my above example, the person could have 3.5% in the X cell and have 18% in the Y cell. The formula would then generate the multiplier (Z) of 1.25

Thank you very much for the help!!!! My initial thought is that it would have to do with an If(and(formula)...but I can<t seem to make it work

 

[Dryver14]

Better off setting table up as follows

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		1.99	4.99	7.99	10.99	50
2	0	0.6	0.8	1	1	1.25		45	2
3	14.99	0.8	1	1	1.25	1.35		15
4	24.99	1	1.25	1.25	1.35	1.5
5	35	1.25	1.35	1.5	2	2.25
6
Sheet1

 

[mikevalsoft]

I cant seem to make it work..

 

[mikevalsoft]

Phantom Shares	Organic ARR Growth Last 12 months %
EBITDA Margin Last 6 months %		1.99	4.99	7.99	10.99	50
9.99	0.6	0.8	1	1	1.25
14.99	0.8	1	1	1.25	1.35
24.99	1	1.25	1.25	1.35	1.5
34.99	1	1.25	1.35	1.5	2
50	1.25	1.35	1.5	2	2.25

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Dryver14]

not sure where you are going wrong without you posting your formula, the index range is only the inner numbers of the range.

 

[mikevalsoft]

Phantom Shares	Organic ARR Growth Last 12 months %
EBITDA Margin Last 6 months %		1.99	4.99	7.99	10.99	50
9.99	0.6	0.8	1	1	1.25
14.99	0.8	1	1	1.25	1.35
24.99	1	1.25	1.25	1.35	1.5
34.99	1	1.25	1.35	1.5	2
50	1.25	1.35	1.5	2	2.25
12 month organic ARR %	6
6 month EBITDA margin %	15
Multiplier	1		the formula for my multiplier is the following: =INDEX(C14:G18,MATCH(D20,C13:G13,1),MATCH(D21,B14:B18,1)) with a 6% organic growth and a 15% EBITDA, the multiplier value should be 1.25....why does it give 1?

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[mikevalsoft]

@Dryver14, is there a way I can copy and paste my spreadsheet? I would make it a lot easier

 

[mikevalsoft]

@Dryver14, the match formula matches the biggest number that is LESS THAN the lookup value...hence my index value is always wrong by one cell

 

[Dryver14]

the index range is the 0.6 - 2.25 the first match is 9.99-50 the second match is 1.99-10.99

 

[mikevalsoft]

Could you please try your formula with the lookup values of 1 and 8 respectively? You will see that the answer to your formula is #N/A