I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in column
"G" in "Sheet1" that contains the same value as in "B4" and return the value
in column "W" in "Sheet1". My problem is that the numbers in column "W" are
unique but not the numbers in column "G".
In the next cell I want to do the same and also look for another cell in
Column "G" in "Sheet1" with the same value as in "B4", but I will always get
the first found result. I want to look for the next cell in column "G" in
"Sheet1" that contains the same value as in "B4" but with another value in
Column "W" than the first found.

Is my question clear? Probably not, but I would very much appreciate if some
one could help me.
/Confused

Here is a formula that I think works, courtesy of Domenic. Put it in F4 and
copy across until you get a blank (no more)

=IF(COLUMN()-COLUMN($F4)+1<=COUNTIF(Sheet1!$G$2:$G$6,$B4),INDEX (Sheet1!$W$2:
$W$6,SMALL(IF(Sheet1!$G$2:$G$6=$B4,ROW(Sheet1!$G$2 :$G$6)-CELL("row",Sheet1!$
G$2)+1),COLUMN()-COLUMN($F4)+1)),"")

It is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"Confused" wrote in message
...
I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in column
"G" in "Sheet1" that contains the same value as in "B4" and return the
value
in column "W" in "Sheet1". My problem is that the numbers in column "W"
are
unique but not the numbers in column "G".
In the next cell I want to do the same and also look for another cell in
Column "G" in "Sheet1" with the same value as in "B4", but I will always
get
the first found result. I want to look for the next cell in column "G" in
"Sheet1" that contains the same value as in "B4" but with another value in
Column "W" than the first found.

Is my question clear? Probably not, but I would very much appreciate if
some
one could help me.
/Confused

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


"Bob Phillips" wrote:

Here is a formula that I think works, courtesy of Domenic. Put it in F4 and
copy across until you get a blank (no more)

=IF(COLUMN()-COLUMN($F4)+1<=COUNTIF(Sheet1!$G$2:$G$6,$B4),INDEX (Sheet1!$W$2:
$W$6,SMALL(IF(Sheet1!$G$2:$G$6=$B4,ROW(Sheet1!$G$2 :$G$6)-CELL("row",Sheet1!$
G$2)+1),COLUMN()-COLUMN($F4)+1)),"")

It is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"Confused" wrote in message
...
I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in column
"G" in "Sheet1" that contains the same value as in "B4" and return the
value
in column "W" in "Sheet1". My problem is that the numbers in column "W"
are
unique but not the numbers in column "G".
In the next cell I want to do the same and also look for another cell in
Column "G" in "Sheet1" with the same value as in "B4", but I will always
get
the first found result. I want to look for the next cell in column "G" in
"Sheet1" that contains the same value as in "B4" but with another value in
Column "W" than the first found.

Is my question clear? Probably not, but I would very much appreciate if
some
one could help me.
/Confused

In what way does it not work?

--
HTH

Bob Phillips

"Confused" wrote in message
...
Thank you very much for your help, but I am sorry this is not working.


"Bob Phillips" wrote:

Here is a formula that I think works, courtesy of Domenic. Put it in F4
and
copy across until you get a blank (no more)


=IF(COLUMN()-COLUMN($F4)+1<=COUNTIF(Sheet1!$G$2:$G$6,$B4),INDEX (Sheet1!$W$2:

$W$6,SMALL(IF(Sheet1!$G$2:$G$6=$B4,ROW(Sheet1!$G$2 :$G$6)-CELL("row",Sheet1!$
G$2)+1),COLUMN()-COLUMN($F4)+1)),"")

It is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"Confused" wrote in message
...
I use "=VLOOKUP($B$4,Sheet1!$G$2:$W$137,17,FALSE)" to find cells in
column
"G" in "Sheet1" that contains the same value as in "B4" and return the
value
in column "W" in "Sheet1". My problem is that the numbers in column
"W"
are
unique but not the numbers in column "G".
In the next cell I want to do the same and also look for another cell
in
Column "G" in "Sheet1" with the same value as in "B4", but I will
always
get
the first found result. I want to look for the next cell in column "G"
in
"Sheet1" that contains the same value as in "B4" but with another
value in
Column "W" than the first found.

Is my question clear? Probably not, but I would very much appreciate
if
some
one could help me.
/Confused

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!

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!

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

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

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!

Thanks, though this excel-file will be used from many different computers and
I can't make sure that your file will be downloaded to all these computers.

Any other suggestions?

"Alan Beban" wrote:

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!

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

Domenic's array formula can be generalized, with

luVal referring to the cell containing the lookup value
Tbl referring to the range of the lookup table
luCol referring to the cell containing the lookup column
from which the value is to be returned

=IF(ROWS(A$1:A1)<=COUNTIF(INDEX(Tbl,0,1),luVal),IN DEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)=luV al,ROW(INDIRECT("A1:A"&ROWS(Tbl)))),ROWS(A$1:A1))) ,"")

A portion of it:

=INDEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)= luVal,ROW(INDIRECT("A1:A"&ROWS(Tbl)))),ROWS(A$1:A1 )))

is a built-in equivalent of the non-array VLookups formula

=INDEX(VLookups(luVal,Tbl,luCol),ROW(A1))

With the built-in formulas one can substitute an array of column numbers
for luCol.

Alan Beban

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

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