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

Hi Domenic,

Thank you for reply.

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.

Assuming E1:F10 contains your data...


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...

Assuming E1:F10 contains your data...

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