Help with LOOKUP/MATCH

[Dexx]

I attacched a sample.

The problem is that the data is formatted like this

Please Login or Register  to view this content.

I need:
Max Sent = User D
Max Recived = User A,User B (or if is too difficult just the first occurence)

I tried using LOOKUP, MATCH, INDEX MAX with no results

Thanx

[daddylonglegs]

You could use this formula in N11

=INDEX(F4:F14,MATCH(1,(G5:G15="sent")*(H5:H15=MAX(IF(G5:G15="sent",H5:H15))),0))

confirmed with CTRL+SHIFT+ENTER

It'll only give you one user name, if there's a tie the first one

the assumption is that the user name will always be one cell to the left and one above the relevant "sent". Similarly for "Received" [I'm assuming it'll be correctly spelt ]

=INDEX(F3:F13,MATCH(1,(G5:G15="received")*(H5:H15=MAX(IF(G5:G15="received",H5:H15))),0))

also confirmed with CTRL+SHIFT+ENTER

You could pull out multiple names when there are ties for MAX, how many users will there be in reality? Presumably more than 4 as per your example

[oldchippy]

Try this for MAX Sent = User D

=INDIRECT(ADDRESS(MATCH(MAX($H$4:$H$15),$H$4:$H$15,0)+2,6))

[Dexx]

Try this for MAX Sent = User D

=INDIRECT(ADDRESS(MATCH(MAX($H$4:$H$15),$H$4:$H$15,0)+2,6))

Hi oldchippy, your formula doesn't works 'cause also the received are included in it.

@daddylonglegs

Wow it works!
Thanx, yeah, sorry for "recived" copy/paste error

The number of users is 17

[daddylonglegs]

If you want to return multiple names when there is a tie then it gets a little complicated. You can return the names in one cell, comma separated if you use the MCONCAT function from the Morefunc add-in.

Get Morefunc here

http://longre.free.fr/

then use

=SUBSTITUTE(MCONCAT(IF(G5:G15="sent",IF(H5:H15=MAX(IF(G5:G15="sent",H5:H15)),", "&F4:F14,""),"")),", ","",1)

confirmed with CTRL+SHIFT+ENTER

or, without Morefunc, you could get names in separate cells if you put this formula in N11

=IF(SUM(IF($G5:$G15="sent",IF($H5:$H15=MAX(IF($G5:$G15="sent",$H5:$H15)),1)))>COLUMN()-COLUMN($N11),INDEX($F4:$F14,SMALL(IF($G5:$G15="sent",IF($H5:$H15=MAX(IF($G5:$G15="sent",$H5:$H15)),ROW($H5:$H15)-ROW(H$5)+1)),COLUMN()-COLUMN($N11)+1)),"")

confirmed with CTRL+SHIFT+ENTER

and copied across (i.e. to O11, P11 etc.)

[Dexx]

the second method works like a charm. I think I will use it
Thanx again, dunno how you calculated that formula :D

[daddylonglegs]

actaully it could be a little simpler......try

=IF(SUM(IF($H5:$H15=MAX(($G5:$G15="sent")*($H5:$H15)),1))>COLUMN()-COLUMN($N11),INDEX($F4:$F14,SMALL(IF($H5:$H15=MAX(($G5:$G15="sent")*($H5:$H15)),ROW($H5:$H15)-ROW(H$5)+1),COLUMN()-COLUMN($N11)+1)),"")

still confirmed with CTRL+SHIFT+ENTER and copied across