Multiple IF with individual criteria for rate with results return value 'place' in header

[Greenbehindthecells]

Hello,

As you have imagined, I am having an issue with something I am trying to do in Excel. I am working on a 'dashboard'. I need to find the 'place ranking' for the rate I am returning from a cell. I tried an IFS OR formula, but I think I am approaching this incorrectly.

The individual formulas work, but I need this consolidated into one cell for this project. The screenshot shows E2:M2 are the individual formulas that show the 'Place' as 9th, 8th, 7th…etc that I want to consolidate into one cell. What is frustrating is recall doing this 3-4 years ago for a different company and I cant locate or remember how I got it to work. Can anyone point me in the right direction?

Places.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Num	Den	Rate	9th	8th	7th	6th	5th	4th	3rd	2nd	1st		I am not able to combine this into a single IF OR statement, it could be the wrong approach?
2	BC	370	370	100.00%	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	1st		#N/A
3	CC	100	370	27.03%	9th	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
4	CH	200	370	54.05%	#N/A	#N/A	#N/A	#N/A	#N/A	4th	#N/A	#N/A	#N/A
5	CO	200	370	54.05%	#N/A	#N/A	#N/A	6th	#N/A	#N/A	#N/A	#N/A	#N/A
6					E2:M2 are the individual formulas that show the 'Place' as 9th, 8th, 7th…etc
7					I do not want 9 different cells.
8
9		9th	8th	7th	6th	5th	4th	3rd	2nd	1st
10	BC	52.77%	58.70%	63.87%	65.82%	68.95%	70.74%	73.26%	78.09%	79.37%
11	CC	34.31%	40.15%	50.61%	53.80%	59.17%	64.48%	67.16%	72.38%	76.12%
12	CH	29.77%	32.50%	37.58%	40.35%	44.54%	51.43%	55.95%	64.52%	70.27%
13	CO	34.57%	39.05%	49.39%	52.80%	60.10%	64.75%	66.10%	71.34%	74.32%
14
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=B2/C2
E2:E5	E2	=IFS($D2<=B10,$B$9)
F2:L5	F2	=IFS(AND($D2>=C10,$D2<D10),C$9)
M2:M5	M2	=IFS(AND($D2>=J10),$J$9)
O2	O2	=IFS(OR($D2<=B10),$B$9),IFS(OR($D2>=C10,$D2<D10),C$9),IFS(OR($D2>=D10,$D2<E10),D$9),IFS(OR($D2>=E10,$D2<F10),E$9),IFS(OR($D2>=F10,$D2<G10),F$9),IFS(OR($D2>=G10,$D2<H10),G$9),IFS(OR($D2>=H10,$D2<I10),H$9),IFS(OR($D2>=I10,$D2<J10),I$9),IFS(OR($D2>=J10),$J$9)

 

[DanteAmor]

Hello @Greenbehindthecells .

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

If the values in cells A2 to A5 match in position, i.e. 1, 2, 3 and 4 with cells A10 to A13, then this is enough:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1		Num	Den	Rate	Place
2	BC	370	370	100.00%	1st
3	CC	100	370	27.03%	9th
4	CH	200	370	54.05%	4th
5	CO	200	370	54.05%	6th
6
7
8
9		9th	8th	7th	6th	5th	4th	3rd	2nd	1st
10	BC	52.77%	58.70%	63.87%	65.82%	68.95%	70.74%	73.26%	78.09%	79.37%
11	CC	34.31%	40.15%	50.61%	53.80%	59.17%	64.48%	67.16%	72.38%	76.12%
12	CH	29.77%	32.50%	37.58%	40.35%	44.54%	51.43%	55.95%	64.52%	70.27%
13	CO	34.57%	39.05%	49.39%	52.80%	60.10%	64.75%	66.10%	71.34%	74.32%
Hoja16

Cell Formulas
Range	Formula
D2:D5	D2	=B2/C2
E2:E5	E2	=INDEX($B$9:$J$9,,IFERROR(MATCH($D2,$B10:$J10,1),1))

But if the values don't match then use the following. For example. The values in the box below do not match the positions of the values in the box above.
Then the formula looks for which is the corresponding row. I highlighted them in colors:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1		Num	Den	Rate	Place
2	BC	370	370	100.00%	1st
3	CC	100	370	27.03%	9th
4	CH	200	370	54.05%	4th
5	CO	200	370	54.05%	6th
6
7
8
9		9th	8th	7th	6th	5th	4th	3rd	2nd	1st
10	CH	29.77%	32.50%	37.58%	40.35%	44.54%	51.43%	55.95%	64.52%	70.27%
11	BC	52.77%	58.70%	63.87%	65.82%	68.95%	70.74%	73.26%	78.09%	79.37%
12	CO	34.57%	39.05%	49.39%	52.80%	60.10%	64.75%	66.10%	71.34%	74.32%
13	CC	34.31%	40.15%	50.61%	53.80%	59.17%	64.48%	67.16%	72.38%	76.12%
Hoja16

Cell Formulas
Range	Formula
D2:D5	D2	=B2/C2
E2:E5	E2	=INDEX($B$9:$J$9,,IFERROR(MATCH($D2, INDEX($B$10:$J$13,MATCH(A2,$A$10:$A$13,0),0),1),1))

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Greenbehindthecells]

Hello @Greenbehindthecells .

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

If the values in cells A2 to A5 match in position, i.e. 1, 2, 3 and 4 with cells A10 to A13, then this is enough:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1		Num	Den	Rate	Place
2	BC	370	370	100.00%	1st
3	CC	100	370	27.03%	9th
4	CH	200	370	54.05%	4th
5	CO	200	370	54.05%	6th
6
7
8
9		9th	8th	7th	6th	5th	4th	3rd	2nd	1st
10	BC	52.77%	58.70%	63.87%	65.82%	68.95%	70.74%	73.26%	78.09%	79.37%
11	CC	34.31%	40.15%	50.61%	53.80%	59.17%	64.48%	67.16%	72.38%	76.12%
12	CH	29.77%	32.50%	37.58%	40.35%	44.54%	51.43%	55.95%	64.52%	70.27%
13	CO	34.57%	39.05%	49.39%	52.80%	60.10%	64.75%	66.10%	71.34%	74.32%
Hoja16

Cell Formulas
Range	Formula
D2:D5	D2	=B2/C2
E2:E5	E2	=INDEX($B$9:$J$9,,IFERROR(MATCH($D2,$B10:$J10,1),1))

But if the values don't match then use the following. For example. The values in the box below do not match the positions of the values in the box above.
Then the formula looks for which is the corresponding row. I highlighted them in colors:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1		Num	Den	Rate	Place
2	BC	370	370	100.00%	1st
3	CC	100	370	27.03%	9th
4	CH	200	370	54.05%	4th
5	CO	200	370	54.05%	6th
6
7
8
9		9th	8th	7th	6th	5th	4th	3rd	2nd	1st
10	CH	29.77%	32.50%	37.58%	40.35%	44.54%	51.43%	55.95%	64.52%	70.27%
11	BC	52.77%	58.70%	63.87%	65.82%	68.95%	70.74%	73.26%	78.09%	79.37%
12	CO	34.57%	39.05%	49.39%	52.80%	60.10%	64.75%	66.10%	71.34%	74.32%
13	CC	34.31%	40.15%	50.61%	53.80%	59.17%	64.48%	67.16%	72.38%	76.12%
Hoja16

Cell Formulas
Range	Formula
D2:D5	D2	=B2/C2
E2:E5	E2	=INDEX($B$9:$J$9,,IFERROR(MATCH($D2, INDEX($B$10:$J$13,MATCH(A2,$A$10:$A$13,0),0),1),1))

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Thank you very much! The last (out of order) works great. I am not so familiar with the index match functions. Now I am trying to reverse-calculate a numerator for the desired rate place (8th, 7th, etc). Could I also use an index match formula for this purpose?

 

[Greenbehindthecells]

Please excuse me, I do not think index/match will work to find the correct numerator to reach the desired rate. Thank you again!

 

[Sam_D_Ben]

How about modify your formula to

=IF(OR($D2<=B10),$B$9,IF(AND($D2>=C10,$D2<D10),C$9,IF(AND($D2>=D10,$D2<E10),D$9,IF(AND($D2>=E10,$D2<F10),E$9,IF(AND($D2>=F10,$D2<G10),F$9,IF(AND($D2>=G10,$D2<H10),G$9,IF(AND($D2>=H10,$D2<I10),H$9,IF(AND($D2>=I10,$D2<J10),I$9,IF(AND($D2>=J10),$J$9)))))))))

Please try

 

[Greenbehindthecells]

Thank you so much! It works very well.

 

[DanteAmor]

Please excuse me, I do not think index/match will work to find the correct numerator to reach the desired rate. Thank you again!

You need to give an example of what you have and what you want as a result.
But I'm afraid it's a new problem. I ask you to please create a new thread and there you describe with examples what you need.

Have a nice day!

 

[Greenbehindthecells]

You need to give an example of what you have and what you want as a result.
But I'm afraid it's a new problem. I ask you to please create a new thread and there you describe with examples what you need.

Have a nice day!

Thank you, you have helped me so much! My other question didn't really make sense, I realized it after but could not edit it. I hope you have a great day!