MATCH w/ multiple answers?

[fastballfreddy]

Ok here's what I got:

A. I have cells: D40, G40, J40, M40, P40, S40, V40, Y40, AB40, AE40, which have varius numbers in them.

B. I have cells: D34, G34, J34, M34, P34, S34, V34, Y34, AB34, AE34, which have varius names in them.

In cell G53 I need a formula that finds the MAX for D40, G40, M40 and so on

Let's say the max is 3, then search cells D34, G34, J34 and so on and if that cell has a 3 display the name. This is the formula I have:

=INDEX($D$34:$AS$34,MATCH(MAX(D40:AS40),D40:AS40,0))

Only problem is if there are multiple answers, it only displays the first cell that is true. In my formula D34, M34 and S34 all equal 3, but cell G53 just shows D34. Can someone help?

Thanks

[fastballfreddy]

Here's a sample

[Biff]

Hi!

It would be easier to work with a xls file. Can you post an xls file?

Biff

"fastballfreddy"
<fastballfreddy.27lply_1147280405.7168@excelforum-nospam.com> wrote in
message news:fastballfreddy.27lply_1147280405.7168@excelforum-nospam.com...
>
> Here's a sample
>
>
> +-------------------------------------------------------------------+
> |Filename: web2.doc |
> |Download: http://www.excelforum.com/attachment.php?postid=4743 |
> +-------------------------------------------------------------------+
>
> --
> fastballfreddy
> ------------------------------------------------------------------------
> fastballfreddy's Profile:
> http://www.excelforum.com/member.php...o&userid=33986
> View this thread: http://www.excelforum.com/showthread...hreadid=540574
>

[fastballfreddy]

I tried, but it says invalid file.

Here's a link where it is posted

http://www.ozgrid.com/forum/showthre...d=1#post257435

[Biff]

Can't open the ozgrid link unless you're a member!

You can upload to excelforum if you zip the file.

Basically, what you want to do will require that you return multiple values
to multiple cells. Not really difficult but I just would like to see where
to put these multiple results.

Biff

"fastballfreddy"
<fastballfreddy.27lsdz_1147284003.8483@excelforum-nospam.com> wrote in
message news:fastballfreddy.27lsdz_1147284003.8483@excelforum-nospam.com...
>
> I tried, but it says invalid file.
>
> Here's a link where it is posted
>
> http://www.ozgrid.com/forum/showthre...d=1#post257435
>
>
> --
> fastballfreddy
> ------------------------------------------------------------------------
> fastballfreddy's Profile:
> http://www.excelforum.com/member.php...o&userid=33986
> View this thread: http://www.excelforum.com/showthread...hreadid=540574
>

[fastballfreddy]

Here you go

[Biff]

Ah man, merged cells! buzz kill!

The merged cells will make this a nightmare!

If you want this to work you need to redesign and NOT USE MERGED CELLS.

Biff

"fastballfreddy"
<fastballfreddy.27m7fm_1147303503.6782@excelforum-nospam.com> wrote in
message news:fastballfreddy.27m7fm_1147303503.6782@excelforum-nospam.com...
>
> Here you go
>
>
> +-------------------------------------------------------------------+
> |Filename: web.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4746 |
> +-------------------------------------------------------------------+
>
> --
> fastballfreddy
> ------------------------------------------------------------------------
> fastballfreddy's Profile:
> http://www.excelforum.com/member.php...o&userid=33986
> View this thread: http://www.excelforum.com/showthread...hreadid=540574
>

[fastballfreddy]

Ok, I can undo the merged cells, but what would I use for a formula? Let's just say the names are in A1-A10, the #s in B1-B10. What formula would you use in C1?

thanks

[Biff]

I put together a little sample file that demonstrates this.

Send me an email:

xl can help at comcast period net

Remove "can" and change the obvious.

This requires an array formula and you can't enter array formulas in merged
cells. Merged cells really don't do anything but cause problems!

Biff

"fastballfreddy"
<fastballfreddy.27ml3a_1147321202.2769@excelforum-nospam.com> wrote in
message news:fastballfreddy.27ml3a_1147321202.2769@excelforum-nospam.com...
>
> Ok, I can undo the merged cells, but what would I use for a formula?
> Let's just say the names are in A1-A10, the #s in B1-B10. What formula
> would you use in C1?
>
> thanks
>
>
> --
> fastballfreddy
> ------------------------------------------------------------------------
> fastballfreddy's Profile:
> http://www.excelforum.com/member.php...o&userid=33986
> View this thread: http://www.excelforum.com/showthread...hreadid=540574
>