Formula

[ozcank]

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]

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]

That works a treat, thanks very much Domenic