MATCH w/ multiple answers?

F

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
 
B

Biff

Hi!

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

Biff

"fastballfreddy"
 
B

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"
 
B

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"
 
F

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
 
B

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"
 
Top