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"--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 lookup 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? How shoul
I correct the formula? Thanks

 

[Morrigan]

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

 

[Biff]

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]

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

 

[marlea]

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