Cow Girl;399142 Wrote:
I need some help and haven't had luck searching for it on here. I have
a
large spreadsheet that has location info in column A. The rest of the
columns
contain the pricing for each location by supplier. My table looks like
this:


Sup 1 Sup 2 Sup 3
PIT 5.50 6.00 6.00
LAX 5.50 5.50 4.50
ORD 6.00 5.25 5.50

In Column E, I'd like the max value, and in Column F, the Supplier
Name(s)
with the max value for each location.

I'm good with Column E (=MAX(B2:D2)), and getting "6", but I'm stuck
on
Column F.

I found this formula: =INDEX($B$1:$D$1,MATCH(MAX($B2:$D2),$B2:$D2,0)),
but
it's only returning "Sup 2" where I'd like to see both Sup 2 & Sup 3. I
have
no problems listing each supplier in a different column, but when I
drag it
over to Column G, I still only get Sup 2.

All help is greatly appreciated!!!

Try in F2:


Code:
--------------------
=IF(COLUMNS($A$1:A$1)COUNTIF($B2:$D2,$E2),"",INDE X($B$1:$D$1,SMALL(IF($B2:$D2=$E2,COLUMN($B2:$D2)-COLUMN($B2)+1),COLUMNS($A$1:A$1))))
--------------------


this assumes data is in B2:D2, Max is in E2 and headers are in B1:D1...
adjust ranges to suit.. and then confirm the formula with
CTRL+SHIFT+ENTER not just enter... then copy across and down.


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111402