VLOOKUP OR IFS WITH MATCH FORMULA?????

[JCKCorinna]

I need a formula to add to the yellow highlighted cell (B17) whose result is one of the percentages from F12:J15.

The factors need to match a corresponding row (matching B12 to either D12, D13, D14, D15.)
And from there also matching B8 to the value from the column F11:J11.

So for example, if the RATING IS 89%, AND THE CURRENT % OF MARKET RATE IS 50%, THEN THE VALUE RETURNED SHOULD BE 9%.

If have tried VLOOKUP, IFS, MATCH formulas - I can't seem to find the right combination.
(fingers crossed)

AND, if a formula cannot return a value in B17 - then is there a formula I could add to a conditional formatting formula to at least highlight the correct value in the table?

 

[DanteAmor]

Hi and welcome to MrExcel.

I give you the two solutions, the formula and the conditional format.

Adjust the values that I marked in blue color.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
7	Market	2.00
8	Current	50%
9
10				PERFORMANCE SCALE		% OF MARKET
11						90.99%	91.00%	97.00%	103.10%	110.00%
12		89%		91%	100%	10%	7%	5%	5%	4%
13				71%	90%	9%	6%	4%	4%	3%
14				51%	70%	80%	5%	3%	3%	2%
15				0%	50%	0%	0%	0%	0%	0%
16
17		9%
Hoja4

Cell Formulas
Range	Formula
B17	B17	=INDEX(F12:J15,MATCH(B12,E12:E15,-1),LOOKUP(2,1/(E11:J11<B8),COLUMN(E11:J11)-4))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F12:J15	Expression	=AND($B$12>=$D12,$B$12<=$E12,$B$8<=F$11,$B$8>E$11)	text	NO

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
----- --

 

[DRSteele]

This ought to do it.

MrExcel posts20.xlsx
	B	C	D	E	F	G	H	I	J
8	50%
9
10
11					0%	91%	97%	103.1%	110%
12	89%		90%		10%	7%	5%	5%	4%
13			70%		9%	6%	4%	4%	3%
14			50%		8%	5%	3%	3%	2%
15			0%		0%	0%	0%	0%	0%
16
17	9%
Sheet4

Cell Formulas
Range	Formula
B17	B17	=INDEX(F12:J15,XMATCH(B12,D12:D15,-1,-1),XMATCH(B8,F11:J11,-1))

 

[JCKCorinna]

Hi and welcome to MrExcel.

I give you the two solutions, the formula and the conditional format.

Adjust the values that I marked in blue color.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
7	Market	2.00
8	Current	50%
9
10				PERFORMANCE SCALE		% OF MARKET
11						90.99%	91.00%	97.00%	103.10%	110.00%
12		89%		91%	100%	10%	7%	5%	5%	4%
13				71%	90%	9%	6%	4%	4%	3%
14				51%	70%	80%	5%	3%	3%	2%
15				0%	50%	0%	0%	0%	0%	0%
16
17		9%
Hoja4

Cell Formulas
Range	Formula
B17	B17	=INDEX(F12:J15,MATCH(B12,E12:E15,-1),LOOKUP(2,1/(E11:J11<B8),COLUMN(E11:J11)-4))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F12:J15	Expression	=AND($B$12>=$D12,$B$12<=$E12,$B$8<=F$11,$B$8>E$11)	text	NO

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
----- --A

Hi and welcome to MrExcel.

I give you the two solutions, the formula and the conditional format.

Adjust the values that I marked in blue color.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
7	Market	2.00
8	Current	50%
9
10				PERFORMANCE SCALE		% OF MARKET
11						90.99%	91.00%	97.00%	103.10%	110.00%
12		89%		91%	100%	10%	7%	5%	5%	4%
13				71%	90%	9%	6%	4%	4%	3%
14				51%	70%	80%	5%	3%	3%	2%
15				0%	50%	0%	0%	0%	0%	0%
16
17		9%
Hoja4

Cell Formulas
Range	Formula
B17	B17	=INDEX(F12:J15,MATCH(B12,E12:E15,-1),LOOKUP(2,1/(E11:J11<B8),COLUMN(E11:J11)-4))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F12:J15	Expression	=AND($B$12>=$D12,$B$12<=$E12,$B$8<=F$11,$B$8>E$11)	text	NO

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
----- --

A-MAZE-ING!!!!!! Thank you!

 

[JCKCorinna]

A-MAZE-ING!!!!!
Thank you, Thank you, Thank you!