simple array formula

[luvbite38]

Hi I am using the following array formula but it is resulting in False

{=IF(C20:V20=AE16,(C19:V19))}

what I am trying to do is, if any of the value in C20:V20 range = AE16, return me the value in the adjacent cell of that matched value.

any help plssss....

 

[T. Valko]

Hi I am using the following array formula but it is resulting in False

{=IF(C20:V20=AE16,(C19:V19))}

what I am trying to do is, if any of the value in C20:V20 range = AE16, return me the value in the adjacent cell of that matched value.

any help plssss....

Where do you want the results to appear?

 

[luvbite38]

Thx Mate
In a Cell A1

 

[T. Valko]

Thx Mate
In a Cell A1

Will there be multiple matches? If so, you'll want each match in its own cell, right? If that's the case do you want the results starting in cell A1 and going across row 1 or do you want the results starting in cell A1 and going down column A?

 

[luvbite38]

Thanks once again,

I want them to go downwards.....pls....

 

[Aladin Akyurek]

Hi I am using the following array formula but it is resulting in False

{=IF(C20:V20=AE16,(C19:V19))}

what I am trying to do is, if any of the value in C20:V20 range = AE16, return me the value in the adjacent cell of that matched value.

any help plssss....

Try...

A1:

=COUNTIF(C20:V20,AE16)

A2, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$2:A2)<=$A$1,INDEX($C$19:$V$19,
  SMALL(IF($C$20:$V$20=$AE$16,
   COLUMN($C$20:$V$20)-COLUMN($C$20)+1),ROWS($A$2:A2))),"")

If you are on Excel 2007 or later, we can skip the CountIf bit and starts directly in A1...

=IFERROR(INDEX($C$19:$V$19,SMALL(IF($C$20:$V$20=$AE$16,
  COLUMN($C$20:$V$20)-COLUMN($C$20)+1),ROWS($A$1:A1))),"")

 

[T. Valko]

Thanks once again,

I want them to go downwards.....pls....

Try this...

Enter this formula in B1. This will return the count of records that meet the condition.

=COUNTIF(C20:V20,AE16)

Enter this array formula** in A1 and copy down until you get blanks:

=IF(ROWS(A$1:A1)>B$1,"",INDEX($19:$19,SMALL(IF(C$20:V$20=AE$16,COLUMN(C$20:V$20)),ROWS(A$1:A1))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[luvbite38]

Thank you guys.

much appreciated...