Still not working. I get #VALUE!. I will try to be more specific.
Sheet1:
G H I ...... W
1 123
3 456
1 789
8 111

In Sheet 2 I want to look in "Sheet1" for all cells in Column G that
contains "1" and then return the value in the same row but column W, as below:
123
789

Thanks in advance



"Domenic" wrote:

In article ,
"Confused" wrote:

Thank you very much for your help, but I am sorry this is not working.

Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not
just ENTER. Also, have you adjusted the range for your source data?
Try...

F4, copied across:

=IF(COLUMNS($F4:F4)<=COUNTIF(Sheet1!$G$2:$G$137,$B 4),INDEX(Sheet1!$W$2:$W
$137,SMALL(IF(Sheet1!$G$2:$G$137=$B4,ROW(Sheet1!$G $2:$G$137)-ROW(Sheet1!$
G$2)+1),COLUMNS($F4:F4))),"")

And, of course, if you want your results to be returned in C4, C5, etc.,
change COLUMNS($F4:F4) to COLUMNS($C4:C4).

Hope this helps!