lookup and return multiple values

[Treena]

Hi,

I am trying to lookup a value in a row and return multiple values from another row. I have been successful in looking up a value in a column and returning a value from another column, and do not understand why I am having difficulties repeating the same procedure for rows.

I am using the following function to find multiple values in row2 based on a certain value in row 1 from columns f through k.

={INDEX(F$1:$K$2,SMALL(IF(F$1:$K$2=$A$8,COLUMN(F$1:$K$2)),COLUMN(F:$K)),2)}

Any help would be greatly appreciated, T.H.



EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[T. Valko]

Not exactly sure what you're trying to do but see if this helps:

=IF(COLUMNS($A:A)<=COUNTIF($F$1:$K$1,$A$8),INDEX($F$2:$K$2,SMALL(IF($F$1:$K$1=$A$8,COLUMN($F$2:$K$2)-MIN(COLUMN($F$2:$K$2))+1),COLUMNS($A:A))),"")

Array entered. Copy across until you get blanks.

Biff