Help with INDEX and MATCH?

[samilynn]

Hi, I am using the following formula (combination of INDEX and MATCH) to mark in Column G which Manufacturer has the lowest price.

It works great if the Manufacturers are in columns right next to each other, but it breaks if I have columns in between that I want to ignore.

Is there another way to do this? I would rather not shift Columns C and E out of the table.

Thanks!
Samantha

LOWEST PRICE VENDOR.xlsx
	A	B	C	D	E	F	G
1	SKU	MFG "A"	IGNORE THIS COLUMN	MFG "B"	IGNORE THIS COLUMN	MFG "C"	WHO IS LOWEST?
2	A123	$1.00	$0.10	$1.25	$0.10	$1.50	IGNORE THIS COLUMN
3	A555	$10.00	$0.10	$9.00	$0.10	$9.50	IGNORE THIS COLUMN
4	B456	$0.15	$0.10	$0.17	$0.10	$0.10	IGNORE THIS COLUMN
5	B666	$6.21	$0.10	$6.18	$0.10	$6.99	IGNORE THIS COLUMN
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=INDEX($B$1:$F$1,MATCH(MIN(B2:F2),B2:F2,0))

 

[offthelip]

change your equation to:

=INDEX($B$1:$F$1,MATCH(MIN(B2,D2,F2),B2:F2,0))

 

[Peter_SSs]

1. Is it possible that one of those 'Ignore' column values could be equal to the lowest value in one of the manufacturer columns?
2. What are the actual headers in the 'Ignore' columns? Is there something common about them that is not common to the manufacturer columns?
3. Is it possible that 2 or more manufacturers will have the equal lowest price? If so, what do you want returned in the 'Who is lowest?' column?

 

[samilynn]

change your equation to:

=INDEX($B$1:$F$1,MATCH(MIN(B2,D2,F2),B2:F2,0))

Thank you, this does the trick!!