Lookup function help
 

I'm trying to use the Lookup function to return two values in a row.
E.g.:

worksheet1
column A=lookup values (in this example, the lookup value is "3"--which
is listed twice in the column)

columns B & C=data to be returned

worksheet 2
when the lookup value "3" is found, cells A1 and B1 should contain the
values from Sheet1 cells B1 and C1

when the lookup value "3" is found, cells A2 and B2 should contain the
values from Sheet1 cells B2 and C2

The formula I have is =VLOOKUP(3,Sheet1!A1:C6,{2,3}). In sheet2, I
selected cells A1 and B1 and array entered this formula in the formula
bar.

Result: Excel returns the correct values in A1 and B1, but doesn't find
the other instances of the lookup value. When I select a cell and drag
the formula down, it only copies the values, it doesn't do a look up.
Why is Excel only finding one instance of the lookup value? How should
I correct the formula? Thanks.


--
marlea
------------------------------------------------------------------------
marlea's Profile: http://www.excelforum.com/member.php...o&userid=26209
View this thread: http://www.excelforum.com/showthread...hreadid=398271

Is your lookup table sorted? Did you use the $ to fix your range?


marlea Wrote:
I'm trying to use the Lookup function to return two values in a row.
E.g.:

worksheet1
column A=lookup values (in this example, the lookup value is "3"--which
is listed twice in the column)

columns B & C=data to be returned

worksheet 2
when the lookup value "3" is found, cells A1 and B1 should contain the
values from Sheet1 cells B1 and C1

when the lookup value "3" is found, cells A2 and B2 should contain the
values from Sheet1 cells B2 and C2

The formula I have is =VLOOKUP(3,Sheet1!A1:C6,{2,3}). In sheet2, I
selected cells A1 and B1 and array entered this formula in the formula
bar.

Result: Excel returns the correct values in A1 and B1, but doesn't find
the other instances of the lookup value. When I select a cell and drag
the formula down, it only copies the values, it doesn't do a look up.
Why is Excel only finding one instance of the lookup value? How should
I correct the formula? Thanks.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=398271

Hi!

Try this:

Enter this array formula in Sheet2 A1:

=IF(COUNTIF(Sheet1!$A$1:$A$6,3)=ROWS(Sheet1!$1:1) ,INDEX(Sheet1!B$1:B$6,SMALL(IF(Sheet1!$A$1:$A$6=3, ROW(Sheet1!$1:$6)),ROW(Sheet1!1:1))),"")

Copy across to B1 then down to as many cells that will be needed to cover
all the possible lookup value matches.

You would be better off using a cell to hold the lookup value rather than
hard coding it into the formula.

Biff

"marlea" wrote in
message ...

I'm trying to use the Lookup function to return two values in a row.
E.g.:

worksheet1
column A=lookup values (in this example, the lookup value is "3"--which
is listed twice in the column)

columns B & C=data to be returned

worksheet 2
when the lookup value "3" is found, cells A1 and B1 should contain the
values from Sheet1 cells B1 and C1

when the lookup value "3" is found, cells A2 and B2 should contain the
values from Sheet1 cells B2 and C2

The formula I have is =VLOOKUP(3,Sheet1!A1:C6,{2,3}). In sheet2, I
selected cells A1 and B1 and array entered this formula in the formula
bar.

Result: Excel returns the correct values in A1 and B1, but doesn't find
the other instances of the lookup value. When I select a cell and drag
the formula down, it only copies the values, it doesn't do a look up.
Why is Excel only finding one instance of the lookup value? How should
I correct the formula? Thanks.


--
marlea
------------------------------------------------------------------------
marlea's Profile:
http://www.excelforum.com/member.php...o&userid=26209
View this thread: http://www.excelforum.com/showthread...hreadid=398271

I array entered that formula, but Excel won't compute it--it justs
copies the formula into the cell. What am I doing wrong? Thanks.


--
marlea
------------------------------------------------------------------------
marlea's Profile: http://www.excelforum.com/member.php...o&userid=26209
View this thread: http://www.excelforum.com/showthread...hreadid=398271

After a little tweaking, I got the formula to work. Thanks.


--
marlea
------------------------------------------------------------------------
marlea's Profile: http://www.excelforum.com/member.php...o&userid=26209
View this thread: http://www.excelforum.com/showthread...hreadid=398271