Return a unique value

C

Confused

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
 
B

Bob Phillips

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.
 
D

Domenic

Confused said:
Thank you very much for your help, but I am sorry this is not working.
Click to expand...

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!
 
C

Confused

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
 
C

Confused

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.
 
A

Alan Beban

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
 
C

Confused

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?
 
D

Domenic

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

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!$G$1:$G$4,1),INDEX(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!
 
A

Alan Beban

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),INDEX(INDEX(Tbl,0,luCol),SMALL(IF(INDEX(Tbl,0,1)=luVal,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
 
C

Confused

Thanks a lot! It works perfect.

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

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!$G$1:$G$4,1),INDEX(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!
Click to expand...
 
Top