Return indexed value based on range within a column name on another sheet

[jdk8008]

For this example, I will use fruits and weights to represent the formula I am trying to construct. I would like to return the rank based on the fruit falling within a weight range.

For example, I need the following values returned in Sheet 1 for C2, C3, and C4

C2 = 0 (Because it is greater than or equal to 0, and less than 80 for the Apple column in sheet 2)
C3 = 2 (Because it is greater than or equal to 120, and less than 140 for the Banana column in sheet 2)
C4 = 3 (Because it is greater than or equal to 160 in the Orange column of sheet 2)

I am looking for a formula that I can fill down column C in sheet 1. Note that the weight value will never be less than 0.

Sheet 1

Fruit	Weight	Rank
Apple	70
Banana	120
Orange	170

Sheet 2

Rank	Apple	Banana	Orange
3	120	140	160
2	100	120	140
1	80	100	120
0	0	0	0

Thanks for the help!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Eric W]

Try:

Book4
	A	B	C	D
1	Rank	Apple	Banana	Orange
2	3	120	140	160
3	2	100	120	140
4	1	80	100	120
5	0	0	0	0
Sheet2

Book1
	A	B	C
1	Fruit	Weight	Rank
2	Apple	70	0
3	Banana	120	2
4	Orange	170	3
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=MAX(Sheet2!$A$2:$A$5*(Sheet2!$B$2:$D$5<=B2)*(Sheet2!$B$1:$D$1=A2))
Press CTRL+SHIFT+ENTER to enter array formulas.

Edit: This doesn't require CSE:

=AGGREGATE(14,4,Sheet2!$A$2:$A$5*(Sheet2!$B$2:$D$5<=B2)*(Sheet2!$B$1:$D$1=A2),1)

 

[jdk8008]

Try:

Book4
	A	B	C	D
1	Rank	Apple	Banana	Orange
2	3	120	140	160
3	2	100	120	140
4	1	80	100	120
5	0	0	0	0
Sheet2

Book1
	A	B	C
1	Fruit	Weight	Rank
2	Apple	70	0
3	Banana	120	2
4	Orange	170	3
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=MAX(Sheet2!$A$2:$A$5*(Sheet2!$B$2:$D$5<=B2)*(Sheet2!$B$1:$D$1=A2))
Press CTRL+SHIFT+ENTER to enter array formulas.

Edit: This doesn't require CSE:

=AGGREGATE(14,4,Sheet2!$A$2:$A$5*(Sheet2!$B$2:$D$5<=B2)*(Sheet2!$B$1:$D$1=A2),1)

This worked perfectly, thanks!

 

[Peter_SSs]

Another possibility to consider ..

1. Sort your Sheet2 table with Rank ascending and make that table a formal Excel table.
   
   

   jdk8008.xlsm
   	A	B	C	D
   1	Rank	Apple	Banana	Orange
   2	0	0	0	0
   3	1	80	100	120
   4	2	100	120	140
   5	3	120	140	160
   Sheet2

   
   
   
2. Then use this in Sheet1. This would automatically adapt ranges if the number of fruits or ranks in Sheet2 changed.
   
   

   jdk8008.xlsm
   	A	B	C
   1	Fruit	Weight	Rank
   2	Apple	70	0
   3	Banana	120	2
   4	Orange	170	3
   Sheet1

   Cell Formulas
   Range	Formula
   C2:C4	C2	=INDEX(Table1[Rank],MATCH(B2,INDIRECT("Table1["&A2&"]")))