Formula for finding value in single row and then returning the value of the cell 2 to the left in the same row

[newnova]

Hi all,

I can't seem to figure out how to make VLOOKUP or HLOOKUP work in this circumstance: to find a value and return a related value from the same array (a single row).

Example:

Product Code1	Location1	Quantity1	Product Code2	Location2	Quantity2	Product Code3	Location3	Quantity3	Product Code4	Location4	Quantity4
BANAN1	Aisle 13	5	BANAN2	Aisle 10	10	BANAN3	Aisle 20	20	BANAN4	Aisle 3​	21

I want to write 3 separate formulas that find the highest quantity in a single row (which I have done via index match max), but also that pull the product code and location code which correspond with that highest quantity from the same row. So that I can add three columns to the end of the row with Best Product Code, Best Location, Best Quantity. In the above example, Best product code should be BANAN4, Best Location should be Aisle 3 and Best Quantity (which I've already figured out) should be 21.

Does anyone know if I'm on the right track using HLOOKUP and VLOOKUP or if I should be approaching this problem differently altogether?

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Product Code1	Location1	Quantity1	Product Code2	Location2	Quantity2	Product Code3	Location3	Quantity3	Product Code4	Location4	Quantity4
2	BANAN1	Aisle 13	5	BANAN2	Aisle 10	10	BANAN3	Aisle 20	20	BANAN4	Aisle 3	21		BANAN4	Aisle 3	21
Primary

Cell Formulas
Range	Formula
N2:P2	N2	=INDEX(A2:L2,MATCH(MAX(A2:L2),C2:L2,0)+{0,1,2})
Dynamic array formulas.

 

[RasGhul]

Maybe this,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Product Code1	Location1	Quantity1	Product Code2	Location2	Quantity2	Product Code3	Location3	Quantity3	Product Code4	Location4	Quantity4
2	BANAN1	Aisle 13	5	BANAN2	Aisle 10	10	BANAN3	Aisle 20	20	BANAN4	Aisle 3	21
3
4
5	Best Product	BANAN4
6	Best Location	Aisle 3
7	Best Qty	21
Sheet1

Cell Formulas
Range	Formula
B5	B5	=INDEX(Product,MATCH(MAX(Product),Product,0)-2)
B6	B6	=INDEX(Product,MATCH(MAX(Product),Product,0)-1)
B7	B7	=INDEX(Product,MATCH(MAX(Product),Product,0))

Named Ranges
Name	Refers To	Cells
Product	=Sheet1!$A$2:INDEX(Sheet1!$2:$2,1,COUNTA(Sheet1!$2:$2))	B5:B7