Simple MATCH formula, resulting in Multi Returns.

[JoeBlakkk]

Ive got a Rather Simple Spread of a Win Loss record.

Column A: Name
Column B: Wins
Column C: Losses
Column D: +/- Difference

In Column E I have a small breakdown
that has the Name of the Person with
the most Wins, Losses, Highest and
Lowest Differences.

Everything works pretty good except If
more than one Name in Column A has
a Matching high or low, it only returns
the topmost Name. I'd Like for it to
show all the names.

Code Example:

Wins In Column E(Breakdown)
=INDEX(A4:B253,MATCH(MAX(B4:B253),B4:B253,0)-1,1)

Below in the next cell Ill have the
Win Total to Match the Name

=MAX(B5:B253)

Thanks in Advance!

[NBVC]

You have to be a member to view the attachment... but why not attach a sample directly here which is what we prefer.

[NBVC]

An actual spreadsheet example is what I meant...

[JoeBlakkk]

Sorry, Im new.

[NBVC]

See attached for one way.

I added a popular User-defined function (as a VBA macro)

Please Login or Register  to view this content.

Then applied the formula in E6:

Please Login or Register  to view this content.

and confirmed it with CTRL+SHIFT+ENTER not just ENTER.

I used same formula in other fields, adjusting the references to accomodate what I am looking for.

If there are multiple results, the will appear separated with comma and space.

[JoeBlakkk]

Thanks, that looks great.
The Macro goes abit over my head though, could you break that part down a lil so I can figure out what parts can be adjusted and customized for the whole sheet.

Thanks Again.

[NBVC]

I wouldn't do a good job explaining the actual intricacies of the macro. It is not my macro. It is a macro by Harlan Grove, and is used by many for this purpose of concatenating a range of values...

so it is an alternative really to the CONCAT() function of Excel, but it allows you to supply a range instead of individual cell references comma delimited.

If you look up "aconcat excel" you might find other posts that can give more examples of usage.

The core of the function is this part:

aconcat(IF($D$5:$D$17=E7,$A$4:$A$16,"")

which says to concatenate results from A4:A16 if D5:D16 equal the value in E7...

The actual formula that incorporates this UDF is complex looking in this situation because you have cell entries with spaces in them (separating first/last names)... so I had to manipulate that space first by using a comma as the initial delimiter for the aconcat() function... then subbing a ^ character for the spaces between the first and last names.. then subbing the commas with spaces, then TRIM to get rid of extraneous spaces.. then subbing those left over spaces with a comma and space combination, and finally subbing back a space for all the ^ chars to restore the names as was.

You can read the nested function from inside out to determine the order.