multiple lines
 

I have a master woksheet that I will type an item number into the sheet and
then I want to retrieve data from another worksheet. If I do a vlookup, it
will retrieve the line but my problem is on the worksheet it has multiple
lines. I need to know what formula to use.
Example:
Master Worksheet
(I enter the A1234 and the three lines appear)
A1234 This is a test
This is a big test
This is a small test
On the dataworksheet that I am pulling the info from:
A1234 This is a test
A1234 This is a big test
A1234 This is a small test
I would appreciate anyone's help.
Thanks

Assuming that A1:B1000 contains your data, and that the first row
contains your headers/labels, try the following...

C1: enter your criterion, such as A1234

D1: enter 0 (zero)

D2, copied down:

=IF((A2<"")*(A2=$C$1),LOOKUP(9.99999999999999E+30 7,$D$1:D1)+1,"")

E1:

=MAX(D:D)

F2, copied down:

=IF(ROW()-ROW($F$2)+1<=$E$1,MATCH(ROW()-ROW($F$2)+1,$D:$D,0),"")

G2, copied down:

=IF(N($F2),INDEX(B:B,$F2),"")

Hope this helps!

In article ,
Rhonda wrote:

I have a master woksheet that I will type an item number into the sheet and
then I want to retrieve data from another worksheet. If I do a vlookup, it
will retrieve the line but my problem is on the worksheet it has multiple
lines. I need to know what formula to use.
Example:
Master Worksheet
(I enter the A1234 and the three lines appear)
A1234 This is a test
This is a big test
This is a small test
On the dataworksheet that I am pulling the info from:
A1234 This is a test
A1234 This is a big test
A1234 This is a small test
I would appreciate anyone's help.
Thanks

Let me try to explain it better because now I am lost.

Worksheet 1: Worksheet entry
Row A4


Worksheet 2: Database

A1=A1234 B1=Cold
A2=A1234 B2=Hot
A3=A1234 B3=Mild
A4=B789 B4= Red
A5=B789 B5= White

On Worksheet 1:
When I type A1234 in row A4
I want the following in B2, B3, B4,
B2=Cold (From Worksheet 2, B1)
B3=Hot (From Worksheet 2, B2)
B4=Mild (From Worksheet 2, B3)

On Worksheet 1:
When I type B789 in row A4
I want to see the following in B2, B3, B4,
B2=Red (From Worksheet 2, B4)
B3=White (From Worksheet 2, B5)

Do you know what I mean?




"Domenic" wrote:

Assuming that A1:B1000 contains your data, and that the first row
contains your headers/labels, try the following...

C1: enter your criterion, such as A1234

D1: enter 0 (zero)

D2, copied down:

=IF((A2<"")*(A2=$C$1),LOOKUP(9.99999999999999E+30 7,$D$1:D1)+1,"")

E1:

=MAX(D:D)

F2, copied down:

=IF(ROW()-ROW($F$2)+1<=$E$1,MATCH(ROW()-ROW($F$2)+1,$D:$D,0),"")

G2, copied down:

=IF(N($F2),INDEX(B:B,$F2),"")

Hope this helps!

In article ,
Rhonda wrote:

I have a master woksheet that I will type an item number into the sheet and
then I want to retrieve data from another worksheet. If I do a vlookup, it
will retrieve the line but my problem is on the worksheet it has multiple
lines. I need to know what formula to use.
Example:
Master Worksheet
(I enter the A1234 and the three lines appear)
A1234 This is a test
This is a big test
This is a small test
On the dataworksheet that I am pulling the info from:
A1234 This is a test
A1234 This is a big test
A1234 This is a small test
I would appreciate anyone's help.
Thanks

The solution I provided will do just that. However, it uses several
formulas to achieve what you want. It's a formula system I learned from
Aladin. It's very efficient, especially if you're dealing with a large
amount of data. But if you prefer, you can use the following single
formula instead...

Sheet1!B2, copied down:

=IF(ROWS(Sheet1!B$2:B2)<=COUNTIF(Sheet2!$A$1:$A$5, Sheet1!$A$4),INDEX(Shee
t2!B$1:B$5,SMALL(IF(Sheet2!$A$1:$A$5=Sheet1!$A$4,R OW(Sheet2!$A$1:$A$5)-RO
W(Sheet2!$A$1)+1),ROWS(Sheet1!B$2:B2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Rhonda wrote:

Let me try to explain it better because now I am lost.

Worksheet 1: Worksheet entry
Row A4


Worksheet 2: Database

A1=A1234 B1=Cold
A2=A1234 B2=Hot
A3=A1234 B3=Mild
A4=B789 B4= Red
A5=B789 B5= White

On Worksheet 1:
When I type A1234 in row A4
I want the following in B2, B3, B4,
B2=Cold (From Worksheet 2, B1)
B3=Hot (From Worksheet 2, B2)
B4=Mild (From Worksheet 2, B3)

On Worksheet 1:
When I type B789 in row A4
I want to see the following in B2, B3, B4,
B2=Red (From Worksheet 2, B4)
B3=White (From Worksheet 2, B5)

Do you know what I mean?