Index Match & round vs specific cell by cell calculation

[Greenbehindthecells]

Good afternoon,

I hope I can make this clear... I would like to make a formula to calculate the formula in I2 (Formula sheet) =ROUND(Numbers!K2*(100%-E4)*D4,1)+C4 that doesn't need to be so specific where I have to jump back and forth between my two sheets to locate and select the correct reference cells (Numbers Key column & Place headers) to make this formula work. I have another index match formula that is doing the complex work for me in another column in my 'formula'. sheet I was wondering if looking at these sheets, there is a way to use a formula to search for the correct place header row (j1:s1) in the Numbers sheet and the key column (i2:i24) to replace the first part of my formula '(Numbers!K2...'. I am not at all familiar with index match, I received assistance for that formula. The actual sheet is not as organized as this copy, so it would need to be able to handle that it is out of order. Any assistance will be greatly appreciated!

First sheet 'Numbers:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	C header 1	C header 2	C header 3	C header 4	C header 5	C header 6	C header 7	C header 8	Key	<5th	5th	10th	25th	33rd	50th	66th	75th	90th	95th
2									aa	1.01%	2.01%	3.01%	4.01%	5.01%	6.01%	7.01%	8.01%	9.01%	10.01%
3									bb	11.01%	12.01%	13.01%	14.01%	15.01%	16.01%	17.01%	18.01%	19.01%	20.01%
4									cc	21.01%	22.01%	23.01%	24.01%	25.01%	26.01%	27.01%	28.01%	29.01%	30.01%
5									dd	31.01%	32.01%	33.01%	34.01%	35.01%	36.01%	37.01%	38.01%	39.01%	40.01%
6									ee	41.01%	42.01%	43.01%	44.01%	45.01%	46.01%	47.01%	48.01%	49.01%	50.01%
7									ff	51.01%	52.01%	53.01%	54.01%	55.01%	56.01%	57.01%	58.01%	59.01%	60.01%
8									gg	61.01%	62.01%	63.01%	64.01%	65.01%	66.01%	67.01%	68.01%	69.01%	70.01%
9									hh	1.01%	2.01%	3.01%	4.01%	5.01%	6.01%	7.01%	8.01%	9.01%	10.01%
10									ii	11.01%	12.01%	13.01%	14.01%	15.01%	16.01%	17.01%	18.01%	19.01%	20.01%
11									jj	21.01%	22.01%	23.01%	24.01%	25.01%	26.01%	27.01%	28.01%	29.01%	30.01%
12									kk	31.01%	32.01%	33.01%	34.01%	35.01%	36.01%	37.01%	38.01%	39.01%	40.01%
13									ll	41.01%	42.01%	43.01%	44.01%	45.01%	46.01%	47.01%	48.01%	49.01%	50.01%
14									mm	51.01%	52.01%	53.01%	54.01%	55.01%	56.01%	57.01%	58.01%	59.01%	60.01%
15									nn	61.01%	62.01%	63.01%	64.01%	65.01%	66.01%	67.01%	68.01%	69.01%	70.01%
16									oo	1.01%	2.01%	3.01%	4.01%	5.01%	6.01%	7.01%	8.01%	9.01%	10.01%
17									pp	11.01%	12.01%	13.01%	14.01%	15.01%	16.01%	17.01%	18.01%	19.01%	20.01%
18									qq	21.01%	22.01%	23.01%	24.01%	25.01%	26.01%	27.01%	28.01%	29.01%	30.01%
19									rr	31.01%	32.01%	33.01%	34.01%	35.01%	36.01%	37.01%	38.01%	39.01%	40.01%
20									ss	41.01%	42.01%	43.01%	44.01%	45.01%	46.01%	47.01%	48.01%	49.01%	50.01%
21									tt	51.01%	52.01%	53.01%	54.01%	55.01%	56.01%	57.01%	58.01%	59.01%	60.01%
22									uu	61.01%	62.01%	63.01%	64.01%	65.01%	66.01%	67.01%	68.01%	69.01%	70.01%
23									vv	21.01%	22.01%	23.01%	24.01%	25.01%	26.01%	27.01%	28.01%	29.01%	30.01%
24									ww	31.01%	32.01%	33.01%	34.01%	35.01%	36.01%	37.01%	38.01%	39.01%	40.01%
25
26
Numbers

Second sheet 'formulas'

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2								5th		10th
3		Key	Num	Den	Rate	Percentile		deficit	target	deficit	target
4	Blah1	aa	1	10	10.00%	90th		0.2	1.2
5	Blah2	bb	2	20	10.00%	<5th
6	Blah3	cc	3	30	10.00%	<5th
7	Blah4	dd	4	40	10.00%	<5th
8	Blah5	ee	5	50	10.00%	<5th
9	Blah6	ff	6	60	10.00%	<5th
10	Blah7	gg	7	70	10.00%	<5th
11	Blah8	hh	8	80	10.00%	90th
12	Blah9	ii	9	90	10.00%	<5th
13	Blah10	jj	10	100	10.00%	<5th
14	Blah11	kk	11	110	10.00%	<5th
15	Blah12	ll	12	120	10.00%	<5th
16	Blah13	mm	13	130	10.00%	<5th
17	Blah14	nn	14	140	10.00%	<5th
18	Blah15	oo	15	150	10.00%	90th
19	Blah16	pp	16	160	10.00%	<5th
20	Blah17	qq	17	170	10.00%	<5th
21	Blah18	rr	18	180	10.00%	<5th
22	Blah19	ss	19	190	10.00%	<5th
23	Blah20	tt	20	200	10.00%	<5th
24	Blah21	uu	21	210	10.00%	<5th
25	Blah22	vv	22	220	10.00%	<5th
26	Blah23	ww	23	230	10.00%	<5th
27
Formula

Cell Formulas
Range	Formula
E4:E26	E4	=IFERROR(C4/D4,"0.00%")
F4:F26	F4	=INDEX(Numbers!$J$1:$S$1,,IFERROR(MATCH(Formula!$E4,INDEX(Numbers!$J$2:$S$24,MATCH(Formula!$B4,Numbers!$I$2:$I$24,0),0),1),1))
H4	H4	=I4-C4
I4	I4	=ROUND(Numbers!K2*(100%-E4)*D4,1)+C4

 

[Fluff]

How about

=ROUND(INDEX(Numbers!$J$2:$S$24,MATCH(B4,Numbers!$I$2:$I$24,0),MATCH(F4,Numbers!$J$1:$S$1,0))*(100%-E4)*D4,1)+C4

 

[Greenbehindthecells]

How about

=ROUND(INDEX(Numbers!$J$2:$S$24,MATCH(B4,Numbers!$I$2:$I$24,0),MATCH(F4,Numbers!$J$1:$S$1,0))*(100%-E4)*D4,1)+C4

Thank you. I tried that, but the calculation was off by 2, I am not sure why. I had to use the round function due to finding a .125% discrepancy in the formula I made. I removed the round, but that did not sort out the calculation.

Is the first part 'Numbers!$J$2:$S$24' supposed to be calling to the "<5th" column? I am trying to find what numerator increase is needed for 5ht, 10th, etc places rank.

 

[Fluff]

Is the first part 'Numbers!$J$2:$S$24' supposed to be calling to the "<5th" column?

No it's looking at the column that matches F4

 

[Greenbehindthecells]

Thank you. I tried that, but the calculation was off by 2, I am not sure why. I had to use the round function due to finding a .125% discrepancy in the formula I made. I removed the round, but that did not sort out the calculation.

Is the first part 'Numbers!$J$2:$S$24' supposed to be calling to the "<5th" column? I am trying to find what numerator increase is needed for 5ht, 10th, etc places rank.

I am sorry, I think I understand... the F4 is calling to the current percentile(in the formula's sheet). I am trying to forecast for the next percentile up (the rate in the numbers sheet). I was able to change F4 to the header in the numbers sheet (k1). Thank you so much! I appreciate your time and how much you have helped me! @Fluff

 

[Fluff]

Glad to help & thanks for the feedback.