MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Match More than one


Posted by Sameer Bhide on November 06, 2001 6:30 PM

Consider a table below

value Description
25 A
34 B
32 C
13 D
34 G
32 H
34 K

The objective is to write a formula that looks for a maximum in column - "value" and displays the dats in column "Description" next to it.

In the above example the formula should display the following in one cell.

B G K (- in a single cell with space in b/w each character)

The formula below does that but displays the result only when you evaluate it using F9

(IF(ISERROR(SEARCH(MAX($A$2:$A$8),$A$2:$A$8)),"",$B$2:$B$8))

Is there a workaround or an alternate way of achieving this.

TIA
Sam
Excel 97


Posted by Aladin Akyurek on November 06, 2001 8:58 PM

Besides the need for F9, it doesn't do at all what you want. What you get is

{"";"B";"";"";"G";"";"K"}

which is way off of

B G K

I'll assume your data to be in A1:B8 including the labels.

In C2 enter: =IF(COUNTIF($A$1:$A$8,MAX($A$1:$A$8))>0,INDEX($B$1:$B$8,MATCH(MAX($A$1:$A$8),$A$1:$A$8,0)),"")

In C3 enter: =IF(COUNTIF($A$1:$A$8,MAX($A$1:$A$8))>COUNTA($C$2:C2),C2&" "&INDEX($B$1:$B$8,SUMPRODUCT(MATCH(MAX($A$1:$A$8),INDIRECT(ADDRESS(MATCH(MAX($A$1:$A$8)&"-"&RIGHT(C2),$A$1:$A$8&"-"&$B$1:$B$8,0)+1,1)&":"&"$A$8"),0))+SUMPRODUCT(MATCH(MAX($A$1:$A$8)&"-"&RIGHT(C2),$A$1:$A$8&"-"&$B$1:$B$8,0))),"")

Copy down this mega-formula till it does not produce another result. The last one is what you are looking for.

Regards,

Aladin

Posted by Sameer Bhide on November 07, 2001 1:38 AM


Aladin

Thanks for the reply.Your formula works fine for the above data.
One Problems though.
If the Data in the "Description" Column contains text with more than one character (next to the max values) then the formula returns a #NA

Posted by Aladin Akyurek on November 07, 2001 3:51 AM

I should have known that that one letter descriptions are not structural. :)

In C1 enter: =MAX(A2:A8)
In C2 enter: =COUNTIF(A2:A8,C1)
In C3 enter: =IF(C2,INDEX($B$1:$B$8,MATCH(C1,$A$1:$A$8,0)),"")
In C4 enter: =IF(COUNTA($C$3:C3)<C2,C3&" "&INDEX($B$1:$B$8,SUMPRODUCT(MATCH(C1,INDIRECT(ADDRESS(MATCH(C1&"-"&C3,$A$1:$A$8&"-"&$B$1:$B$8,0)+1,1)&":"&"$A$8"),0))+SUMPRODUCT(MATCH(C1&"-"&C3,$A$1:$A$8&"-"&$B$1:$B$8,0))),"")
In C5 enter: =IF(COUNTA($C$3:C4)<$C$2,C4&" "&INDEX($B$1:$B$8,SUMPRODUCT(MATCH($C$1,INDIRECT(ADDRESS(MATCH($C$1&"-"&SUBSTITUTE(C4,C3&" ",""),$A$1:$A$8&"-"&$B$1:$B$8,0)+1,1)&":"&"$A$8"),0))+SUMPRODUCT(MATCH($C$1&"-"&SUBSTITUTE(C4,C3&" ",""),$A$1:$A$8&"-"&$B$1:$B$8,0))),"")

Copy the last formula down till no additional result is produced.

Aladin

-------------------------

Posted by Aladin Akyurek on November 07, 2001 3:55 AM

I should have known that that one letter descriptions are not structural. :)

In C1 enter: =MAX(A2:A8)
In C2 enter: =COUNTIF(A2:A8,C1)
In C3 enter: =IF(C2,INDEX($B$1:$B$8,MATCH(C1,$A$1:$A$8,0)),"")
In C4 enter: =IF(COUNTA($C$3:C3) < C2,C3&" "&INDEX($B$1:$B$8,SUMPRODUCT(MATCH(C1,INDIRECT(ADDRESS(MATCH(C1&"-"&C3,$A$1:$A$8&"-"&$B$1:$B$8,0)+1,1)&":"&"$A$8"),0))+SUMPRODUCT(MATCH(C1&"-"&C3,$A$1:$A$8&"-"&$B$1:$B$8,0))),"")
In C5 enter: =IF(COUNTA($C$3:C4) < $C$2,C4&" "&INDEX($B$1:$B$8,SUMPRODUCT(MATCH($C$1,INDIRECT(ADDRESS(MATCH($C$1&"-"&SUBSTITUTE(C4,C3&" ",""),$A$1:$A$8&"-"&$B$1:$B$8,0)+1,1)&":"&"$A$8"),0))+SUMPRODUCT(MATCH($C$1&"-"&SUBSTITUTE(C4,C3&" ",""),$A$1:$A$8&"-"&$B$1:$B$8,0))),"") Copy the last formula down till no additional result is produced. Aladin -------------------------

Posted by Sameer bhide on November 07, 2001 4:36 AM

In C1 enter: =MAX(A2:A8)

Aladin
Absolutely Brilliant!!.Thanks a lot. I wanted a solution without restoring to VBA and you have provided just that.

Thanks again

Sam