Formula

Hi All

Is there a formula that looks up a value in a cell in another
worksheet, and returns the values immediately next to it?

What I mean is, I have a sheet that looks like this

Col A Col B Col C

x a 1
x b 2
x c 5
y j 4
y u 8
y k 4
z t 3
z l 5

I have 3 worksheets, one for each of the values in col a (x, y, z).
What I want to be able to do is, pull the values in col b, into it's
relevant worksheet. So in effect, like a vlookup but something that
looks for a value in a cell, and returns all the values associated with
that in another worksheet.

Any help?

Many thanks

Ozkan


--
ozcank


------------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5328
View this thread: http://www.excelforum.com/showthread.php?threadid=381573

Domenic
Thu Jun 23 10:36:35 CDT 2005


Assuming that Sheet1!A1:C7 contains your source table, enter the
following formula in A1 of your other sheet and copy down:

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet1!$A$1:$A$7,"x"),INDEX(Sheet1!B$1:B$7,SMALL(IF(Sheet1!$A$1:$A$7="x",ROW(Sheet1!$A$1:$A$7)-ROW(Sheet1!$A$1)+1),ROWS(A$1:A1))),"")

..confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If you want the
values from Column C to be picked up as well, copy the formula over to
the next column.

Repeat the process for your other values, such as y and z, by changing
="x" to ="y" and then to ="z". In each case, adjust the range for your
source table and references for the sheet names accordingly.

Hope this helps!

ozcank Wrote:
> Hi All
>
> Is there a formula that looks up a value in a cell in another
> worksheet, and returns the values immediately next to it?
>
> What I mean is, I have a sheet that looks like this
>
> Col A Col B Col C
>
> x a 1
> x b 2
> x c 5
> y j 4
> y u 8
> y k 4
> z t 3
> z l 5
>
> I have 3 worksheets, one for each of the values in col a (x, y, z).
> What I want to be able to do is, pull the values in col b, into it's
> relevant worksheet. So in effect, like a vlookup but something that
> looks for a value in a cell, and returns all the values associated with
> that in another worksheet.
>
> Any help?
>
> Many thanks
>
> Ozkan


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10785
View this thread: http://www.excelforum.com/showthread.php?threadid=381573