Bringing back highest value with formula

[adammon]

I'm trying to set up a vlookup formula, or any kind of formula, that works for bringing back the highest value from a data set, as well as the corresponding cell adjacent to the highest value. To give full context, I'm trying to create a sheet for our warehouse to use as a replenishment tool. The logic is: scan in a product SKU, have the file look up against an overstock inventory listing, provide the user with the overstock location and the quantity for that location. The quantity needs to be the highest quantity in the data set and the location needs to be the corresponding location for that value.

Here is the starting point. Right now I just have a basic vlookup against the second data set. As it sits now, it's already bringing back the correct information, but that's only because the highest value is at the top of the list. So I need to make sure that I bring back the highest value in column D, and the pallet location with the highest value in column C.

Any help would be greatly appreciated.

Book1
	A	B	C	D
1	SKU	QTY Needed	UPSTOCK Pallet	Pallet QTY
2	03125-WANAV-L	5	3A3F2	17
3	03125-WANAV-M	2	2B2G2	3
4	03125-WANAV-XL	5	3A3F2	2
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=VLOOKUP(A2,Sheet2!$A$1:$C$11,2,FALSE)
D2:D4	D2	=VLOOKUP(A2,Sheet2!$A$1:$C$11,3,FALSE)

Book1
	A	B	C
1	SKU	Overstock Pallet	Quantity
2	03125-WANAV-L	3A3F2	17
3	03125-WANAV-L	3A3G1	7
4	03125-WANAV-L	2B2G2	1
5	03125-WANAV-M	2B2G2	3
6	03125-WANAV-M	3A9G2	3
7	03125-WANAV-M	1B1F2	1
8	03125-WANAV-XL	3A3F2	2
9	03125-WANAV-XL	3A3G1	18
10	03125-WANAV-XL	1B1F2	2
11	03125-WANAV-XS	4B10G1	10
Sheet2

 

[DanteAmor]

Try this.
The formula in C2 is an arrary formula. Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

Book1
	A	B	C	D
1	SKU	QTY Needed	UPSTOCK Pallet	Pallet QTY
2	03125-WANAV-L	5	3A3F2	17
3	03125-WANAV-M	2	2B2G2	3
4	03125-WANAV-XL	5	3A3G1	18
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	{=INDEX(Sheet2!$B$2:$B$11,MATCH(A2&"|"&D2,Sheet2!$A$2:$A$11&"|"&Sheet2!$C$2:$C$11,0))}
D2:D4	D2	=SUMPRODUCT(MAX((Sheet2!$A$2:$A$11=A2)*(Sheet2!$C$2:$C$11)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

_____________________________________________
You can also use this "regular" formula in C2

=INDEX(Sheet2!$B$2:$B$11,SUMPRODUCT(MAX((Sheet2!$A$2:$A$11=A2)*(Sheet2!$C$2:$C$11=D2)*ROW(Sheet2!$B$2:$B$11)))-1)

 

[Peter_SSs]

Depending on your version of Excel, here are a couple of other options for finding the Pallet Qty and also another non array-entered formula for the UPSTOCK Pallet.

Book1
	A	B	C	D	E
1	SKU	QTY Needed	UPSTOCK Pallet	Pallet QTY	Pallet QTY
2	03125-WANAV-L	5	3A3F2	17	17
3	03125-WANAV-M	2	2B2G2	3	3
4	03125-WANAV-XL	5	3A3G1	18	18
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!A$2:A$11)/((Sheet2!A$2:A$11=A2)*(Sheet2!C$2:C$11=D2)),1))
D2:D4	D2	=MAXIFS(Sheet2!C$2:C$11,Sheet2!A$2:A$11,A2)
E2:E4	E2	=AGGREGATE(14,6,Sheet2!C$2:C$11/(Sheet2!A$2:A$11=A2),1)

You can also use this "regular" formula in C2

=INDEX(Sheet2!$B$2:$B$11,SUMPRODUCT(MAX((Sheet2!$A$2:$A$11=A2)*(Sheet2!$C$2:$C$11=D2)*ROW(Sheet2!$B$2:$B$11)))-1)

Just a comment on this one Dante. If somebody subsequently iserts any new row(s) at the top of Sheet2 this formula would return incorrect values.
You could turn your existing array-entered formula into a non array-entered one and avoid the potential row insertion problem as follows.

=INDEX(Sheet2!$B$2:$B$11,MATCH(A2&"|"&D2,INDEX(Sheet2!$A$2:$A$11&"|"&Sheet2!$C$2:$C$11,0),0))