Lookup Function help

[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"--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.

[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
"marlea" <[email protected]> wrote in
message news:[email protected]...
|
| 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...o&userid=26209
| View this thread: http://www.excelforum.com/showthread...hreadid=398243
|

[Alan Beban]

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