COMPLEX "if" formula

[mikevalsoft]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]< 2%[/TD]
[TD]2 to 5%[/TD]
[TD]5+ to 8%[/TD]
[TD]8+ to 11%[/TD]
[TD]11+%[/TD]
[/TR]
[TR]
[TD]0 to 10%[/TD]
[TD]0.6[/TD]
[TD]0.8[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD]10+ to 15%[/TD]
[TD]0.8[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1.25[/TD]
[TD]1.35[/TD]
[/TR]
[TR]
[TD]15+ to 25%[/TD]
[TD]1[/TD]
[TD]1.25[/TD]
[TD]1.25[/TD]
[TD]1.35[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD]25+ to 35%[/TD]
[TD]1.25[/TD]
[TD]1.35[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]2.25[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl83, width: 64"]Phantom Shares[/TD]
[TD="class: xl72, width: 384, colspan: 6"]Organic ARR Growth Last 12 months %[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"]EBITDA Margin Last 6 months %[/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl87, width: 64"]1.99[/TD]
[TD="class: xl86"]4.99[/TD]
[TD="class: xl86"]7.99[/TD]
[TD="class: xl86"]10.99[/TD]
[TD="class: xl86"]50[/TD]
[/TR]
[TR]
[TD="class: xl84"]9.99[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl76"]1.25[/TD]
[/TR]
[TR]
[TD="class: xl84"]14.99[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl77"]1.35[/TD]
[/TR]
[TR]
[TD="class: xl84"]24.99[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl68"]1.35[/TD]
[TD="class: xl78"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl84"]34.99[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl68"]1.35[/TD]
[TD="class: xl66"]1.5[/TD]
[TD="class: xl78"]2[/TD]
[/TR]
[TR]
[TD="class: xl85"]50[/TD]
[TD="class: xl79"]1.25[/TD]
[TD="class: xl80"]1.35[/TD]
[TD="class: xl81"]1.5[/TD]
[TD="class: xl81"]2[/TD]
[TD="class: xl82"]2.25[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl83, width: 64"]Phantom Shares[/TD]
[TD="class: xl72, width: 384, colspan: 6"]Organic ARR Growth Last 12 months %[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"]EBITDA Margin Last 6 months %[/TD]
[TD="class: xl75"] [/TD]
[TD="class: xl90, width: 64"]1.99[/TD]
[TD="class: xl86"]4.99[/TD]
[TD="class: xl86"]7.99[/TD]
[TD="class: xl86"]10.99[/TD]
[TD="class: xl86"]50[/TD]
[/TR]
[TR]
[TD="class: xl84"]9.99[/TD]
[TD="class: xl66"]0.6[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl76"]1.25[/TD]
[/TR]
[TR]
[TD="class: xl84"]14.99[/TD]
[TD="class: xl66"]0.8[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl77"]1.35[/TD]
[/TR]
[TR]
[TD="class: xl84"]24.99[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl68"]1.35[/TD]
[TD="class: xl78"]1.5[/TD]
[/TR]
[TR]
[TD="class: xl84"]34.99[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl67"]1.25[/TD]
[TD="class: xl68"]1.35[/TD]
[TD="class: xl66"]1.5[/TD]
[TD="class: xl78"]2[/TD]
[/TR]
[TR]
[TD="class: xl85"]50[/TD]
[TD="class: xl79"]1.25[/TD]
[TD="class: xl80"]1.35[/TD]
[TD="class: xl81"]1.5[/TD]
[TD="class: xl81"]2[/TD]
[TD="class: xl82"]2.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl87, colspan: 3"]12 month organic ARR %[/TD]
[TD="class: xl93"]6[/TD]
[TD="class: xl91"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl87, colspan: 3"]6 month EBITDA margin %[/TD]
[TD="class: xl93"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl72, colspan: 3"]Multiplier[/TD]
[TD="class: xl92, align: right"]1[/TD]
[TD]

[/TD]
[TD]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? [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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