# 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)),"")

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

Regards,

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

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 C5 enter: =IF(COUNTA(\$C\$3:C4)&LT;\$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.

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

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) &LT; 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) &LT; \$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)