Lookup Function help

M

marlea

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"--whic
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 th
values from Sheet1 cells B1 and C1

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

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

Result: Excel returns the correct values in A1 and B1, but doesn't fin
the other instances of the look up value. When I select a cell and dra
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? Thanks
 
J

Jim Rech

A VLOOKUP returns the first match in the referenced range. I'm not clear
what you're expecting but a series of VLOOKUPs do not build on each other to
return other matches. Each just returns the first match in its range.

--
Jim
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 look up 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? Thanks.
|
|
| --
| marlea
| ------------------------------------------------------------------------
| marlea's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26209
| View this thread: http://www.excelforum.com/showthread.php?threadid=398243
|
 
A

Alan Beban

marlea said:
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 look up 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? Thanks.
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
array enter into a 2-column range that has sufficient rows to
accommodate all the occurrences of the lookup value

=Vlookups(3,Sheet1!A1:C6,{2,3})

Alan Beban
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top