Thanks a lot! It works perfect.

"Domenic" wrote:

On Sheet2, enter the following formula in A1 and copy down:

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!$G$1:$G$4,1),INDE X(Sheet1!W$1:W$4,SMALL(
IF(Sheet1!$G$1:$G$4=1,ROW(Sheet1!$G$1:$G$4)-ROW(Sheet1!$G$1)+1),ROWS(A$1:
A1))),"")

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER. (Press the CONTROL and SHIFT keys down, then while those keys
are pressed down hit ENTER. Excel will place braces {} around the
formula which will tell you that you've entered the formula correctly.)
Adjust the range accordingly.

Hope this helps!

In article ,
"Confused" wrote:

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