Excel Returns a Value based on multiple criteria

[jdcp]

Hello,

I'm trying to figure out a formula where in Excel will return a result in B12 based on a text value (B10) and a number range (B1. please see table below:

I've been at it for hours and hours using xlookup, vlookup, index, match but could seem to get it to work. I am still a beginner though. any help would be greatly appreciated. thanks!

	BEST	BEST	GOOD	GOOD	BAD	BAD
group A	0%	5	5.01	10%	10.01	100%​
group B	0%	10	10.01	20%	20.01	100%​
group C	0%	20	20.01	30%	30.01	100%​
group D	0%	30	30.01	55%	55.01	100%​
CRITERIAS
GROUP	GROUP B	<---	variable
RANGE	15%​	<---	variable
RATING	GOOD	<---	returned result

 

[Z51]

Convert all of your values in B2:G5 to numbers, e.g. 0, 5, 5.01, 10, 100. Not a mix of numbers and percents (which are decimal numbers between 0 and 1). Then use:

=IF(OR(B10="",B11=""),"",INDEX(B1:G1,MATCH(B11,INDIRECT("B"&MATCH(B10,A1:A5,0)&":G"&MATCH(B10,A1:A5,0)),1)))

 

[jdcp]

Convert all of your values in B2:G5 to numbers, e.g. 0, 5, 5.01, 10, 100. Not a mix of numbers and percents (which are decimal numbers between 0 and 1). Then use:

=IF(OR(B10="",B11=""),"",INDEX(B1:G1,MATCH(B11,INDIRECT("B"&MATCH(B10,A1:A5,0)&":G"&MATCH(B10,A1:A5,0)),1)))

thank you very much, Z51! this solved. I really appreciate the help.

 

[awoohaw]

You had mixed values in your percentages, I changed them to integers. YOu can reformat if you think this works for you:
Indirect is a volatile function and can slow down spreadsheets with lots of lookups.

Mr Excel Questions2.xlsx
	A	B	C	D	E	F	G
1
2	BEST	BEST	GOOD	GOOD	BAD	BAD
3	group A	0.00	5.00	5.01	10.00	10.01	100%
4	group B	0.00	10.00	10.01	20.00	20.01	100%
5	group C	0.00	20.00	20.01	30.00	30.01	100%
6	group D	0.00	30.00	30.01	55.00	55.01	100%
7
8
9
10	CRITERIAS
11	GROUP	GROUP D	<---	variable
12	RANGE	56.00	<---	variable
13	RATING	BAD	<---	returned result
Sheet2

Cell Formulas
Range	Formula
B13	B13	=INDEX($A$2:$F$2,1,MATCH($B$12,OFFSET(INDEX($A$2:$A$6,MATCH($B$11,$A$2:$A$6,0)),0,0,1,COLUMNS($A$2:$F$2))))

 

[jdcp]

You had mixed values in your percentages, I changed them to integers. YOu can reformat if you think this works for you:
Indirect is a volatile function and can slow down spreadsheets with lots of lookups.

Mr Excel Questions2.xlsx
	A	B	C	D	E	F	G
1
2	BEST	BEST	GOOD	GOOD	BAD	BAD
3	group A	0.00	5.00	5.01	10.00	10.01	100%
4	group B	0.00	10.00	10.01	20.00	20.01	100%
5	group C	0.00	20.00	20.01	30.00	30.01	100%
6	group D	0.00	30.00	30.01	55.00	55.01	100%
7
8
9
10	CRITERIAS
11	GROUP	GROUP D	<---	variable
12	RANGE	56.00	<---	variable
13	RATING	BAD	<---	returned result
Sheet2

Cell Formulas
Range	Formula
B13	B13	=INDEX($A$2:$F$2,1,MATCH($B$12,OFFSET(INDEX($A$2:$A$6,MATCH($B$11,$A$2:$A$6,0)),0,0,1,COLUMNS($A$2:$F$2))))

whoa. that seems to work too. today I learned that you can use different formulas and arrive at the same answer. thanks very much too!

 

[awoohaw]

whoa. that seems to work too. today I learned that you can use different formulas and arrive at the same answer. thanks very much too!

in most situations that can happen. But they all have something that help or detracts from your system and solution.
1 + 3 + "ALJD" and SUM(1,3,"ALJD") . Adding with the plus operator will work MOST of the time, but will fail if there is text in one of the cells.

The INDIRECT function is a great function, but because of the way it works it can really bog workbooks down if they have lots of LOOKUP and matching calculations. Try to avoid it when possible.