Index Formula

[Cow Girl]

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(B22)), 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!!!

 

[Eduardo]

Hi,
In column F enter

INDEX($B$1:$D$1,MATCH(E2,$B1:$D1))

 

[Bernard Liengme]

=IF(B2=E2,$B$1&" ","")&IF(C2=E2,$C$1&" ","")&IF(D2=E2,$D$1,"")
best wishes

 

[Cow Girl]

Eduardo - I could not get your formula to work. I'm not sure what I'm doing
wrong.

Bernard - Your formula worked, although I failed to mention that my
spreadsheet actually has about 50 suppliers, so I'm not sure if I could
expand your IF statement out that far. I know that was my bad. Sorry.

Any new ideas?

Thanks,
Cathy

 

[Eduardo]

Hi,
I need a little more information
Your max # will be in column E
What is in column A, (Pit, LAX etc)
Are your suppliers one per column ?, please indicate columns

 

[Cow Girl]

The spreadsheet is actually bigger than what I indicated. I scoped it down to
try to simplify, as I figured I could adjust the column references as needed.
My max will actually be in column BI. Columns A-J are the details of each
location (city, state, zip, etc.). The Suppliers (1 per column) are in
columns K-BH. The maximum number of suppliers that have the same price is 4.
(I did a countif statement in another column to determine this.)

 

[Bernard Liengme]

Here is a UDF that will do the job.
With the data you have, call it on row 2 with =SupMax(B22) to get "Sup 2
Sup 3"

Function SupMax(myrange)
suprow = 1 'This is the row with supplier names
supcol = myrange(1).Column - 1
maxcost = WorksheetFunction.Max(myrange)
For j = 1 To myrange.Count
If myrange(j) = maxcost Then
TempSup = TempSup & Cells(suprow, j + supcol) & " "
End If
Next j
SupMax = TempSup
End Function


New to VBA? Then try these sites
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"
http://www.contextures.com:80/xlvba01.html

best wishes

 

[Cow Girl]

Thanks for your help!!

Bernard - I've almost got your VBA code working, but not quite there. I'm
off to a meeting for the remainder of the day. So, I'll have to get back to
this.

NBVC - I must be having a rough day, as your formula is giving me issues too.

Thank you all though!!

 

[Teethless mama]

In F2:
=IF(COUNTIF($B2:$D2,$E2)>=COLUMNS($A:A),INDEX($B$1:$D$1,SMALL(IF($B2:$D2=$E2,TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS($B$1:$D$1))))),COLUMNS($A:A))),"")

ctrl+shift+enter, not just enter
copy across and down as far as needed

adjust your range to suit