Looking to compare three columns for the cheapest price

**Bayebd24** · 07-05-2012, 02:38 PM

I have a spreadsheet that contains 1300 rows. Each row has three columns of data. Each column has a price from a different vendor.

I would like to compare the three prices for each column to see which vendor is the cheapest.

Item# Vendor 1 Vendor 2 Vendor 3 Cheapest
Books $135 $110 $200 What formula would I put here for each row to find out the cheapest vendor for each item?
Bands $55 $10 $35 What formula would I put here for each row to find out the cheapest vendor for each item?

**Ace_XL** · 07-05-2012, 02:41 PM

=INDEX($A$1:$C$1,0,MATCH(MIN(A2:C2),A2:C2)) for Row 2 and drag down

**NBVC** · 07-05-2012, 02:41 PM

=index($B$1:$D$1,Match(Min($B2:$D2),$B2:$D2,0))

copied down

where B1:D1 contain the vendor names, and B2:D2 have first set of prices.

**NBVC** · 07-05-2012, 02:49 PM

....deleted..... oops

**Bayebd24** · 07-05-2012, 03:40 PM

How do I use conditional formatting to highlight the cell that has the lowest number if it matches the column that found the lowest price using =MIN(H2:J2) to find the lowest price?

**NBVC** · 07-05-2012, 03:51 PM

Use conditional formula:

=H2=MIN($H2:$J2)

**Bayebd24** · 07-05-2012, 06:45 PM

Now,

Can I color code each column so that each vendor has a different color and when I use the conditional formatting option for the last column, it not only brings up the lowest price, but then color codes it to the color for each vendor?

Ive tried changing the code, but it keeps coloring all of the selected cells.
=H2=MIN($H2:$J2)
=I2=MIN($H2:$J2)
=J2=MIN($H2:$J2)

Excel 2003