If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook

=INDEX(VLookups($B$4,Sheet1!$G$2:$W$137,17),ROW(A1 ))

filled down as far as required.

Alan Beban

Confused wrote:
It could work, but I have alot of data so this would actually take a long
time, and the data is quite dynamic so I would need to this often.

Thank you but I would appreciate if someone could help me to solve this more
automatically.

"R.VENKATARAMAN" wrote:


try autofilter custom <equalls =1* in the first column only
then the range is filtered and you can copy these visible cells only to
sheet2
then in sheet1 you can remove the autofilter


Confused wrote in message
...

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, $B4),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!