Look Up Problem - return max number when look multiple look up values are equal

[jamalneedshelpwithexcel]

	a	b	c	d	e
1	CONTRACTED	20	21	21	21
2	Blocked	12	18	18
3	Picked Up	10	24	25	20
4	PICK UP % OF PEAK	50%	120%	125%	100%

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

The goal is to find the largest number in the contract row and return the largest value in the picked up row. In this case 25.

The difficulty is there are equal values in row 1, the number 21 appears three times. Need a look up that will return value in cell d3

 

[jorismoerings]

'Hi,

try this and see if this is what you're after:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	CONTRACTED	20	21	21	21
2	Blocked	12	18	18
3	Picked Up	10	24	25	20
4	PICK UP % OF PEAK	50%	120%	125%	100%
5
6	max value	25
Sheet1

Cell Formulas
Range	Formula
B6	{=MAX(B1:E1*B3:E3)/MAX(B1:E1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jorismoerings]

Addition to #2 an alternative for the array formula could be:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	CONTRACTED	20	21	21	21
2	Blocked	12	18	18
3	Picked Up	10	24	25	20
4	PICK UP % OF PEAK	50%	120%	125%	100%
5
6	max value	25
7	or	25
Sheet1

Cell Formulas
Range	Formula
B7	=AGGREGATE(14,6,(B1:E1*B3:E3)/(B1:E1),1)
B6	{=MAX(B1:E1*B3:E3)/MAX(B1:E1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jamalneedshelpwithexcel]

That formula in B7 is great but the question was bad. The max pick up value returned should be the largest number equal to or less then max contract number. It the example above that number would be a pick up value of 20 (e3) with the contract number of 21. The largest pick up value possible to return in this example would be 21 assuming any of the cells c3:e3 had a value of 21. This way it is possible to have % numbers greater then 100% if the pick up value is greater than the contract value.

 

[jamalneedshelpwithexcel]

Book1
	A	B	C	D
31	CONTRACTED	20	21	21
32	Blocked	12	18	18
33	Picked Up	18	21	22
34	PICK UP % OF PEAK	85.7%	100.0%	104.8%
Master

 

[jorismoerings]

Ahh,
The devil is in the details and the unasked question.

Try this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	CONTRACTED	20	21	21	21
2	Blocked	12	18	18
3	Picked Up	10	24	25	20
4	PICK UP % OF PEAK	50%	120%	125%	100%
5
6	max value	20
7
8	CONTRACTED	20	21	21
9	Blocked	12	18	18
10	Picked Up	18	21	22
11	PICK UP % OF PEAK	85.7%	100.0%	104.8%
12
13	max value	21
Sheet1

Cell Formulas
Range	Formula
B6	=AGGREGATE(14,6,(B3:H3)*(B3:H3<=B1:H1),1)
B13	=AGGREGATE(14,6,(B10:H10)*(B10:H10<=B8:H8),1)