View Single Post
#3
December 30th 05, 05:59 PM posted to microsoft.public.excel.worksheet.functions
 Sam via OfficeKB.com Posts: n/a
Match Single Numeric Criteria and Return Multiple Numeric Labels

Hi Domenic,

Using the Array Formula below, it only returns the first Numeric Label that
matches the criterion. The first matched Numeric Label is returned several
times across the Row.

1) Let H1 contain your criterion, such as 1

2) Enter the following formula in I1 and copy across...

=IF(COLUMNS(\$I1:I1)<=COUNTIF(\$F\$1:\$F\$10,\$H1),INDE X(\$E\$1:\$E\$10,SMALL(IF(\$F
\$1:\$F\$10=\$H1,ROW(\$E\$1:\$E\$10)-ROW(\$E\$1)+1),COLUMNS(\$I1:I1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

I've checked to see if I made any typo's but cannot spot any (yet!).

Would the Worksheet Function TRANSPOSE help?

Any further assistance much appreciated.

Cheers
Sam

Domenic wrote:
I believe having the corresponding values returned to a single cell
would require either VBA or the use of the function MCONCAT which is
available in the free add-in Morefunc.xll.

Since I'm not familiar with VBA, and the add-in is not available for my
Mac version of Excel, I can only offer you a solution where the
corresponding values are returned to individual cells...

1) Let H1 contain your criterion, such as 1

2) Enter the following formula in I1 and copy across...

=IF(COLUMNS(\$I1:I1)<=COUNTIF(\$F\$1:\$F\$10,\$H1),INDE X(\$E\$1:\$E\$10,SMALL(IF(\$F
\$1:\$F\$10=\$H1,ROW(\$E\$1:\$E\$10)-ROW(\$E\$1)+1),COLUMNS(\$I1:I1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi All,

[quoted text clipped - 29 lines]
Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1