Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write code to insert multiple lines | Excel Discussion (Misc queries) | |||
Multiple lines in 1 cell | Excel Discussion (Misc queries) | |||
Is there a way to delete multiple lines at the same time beside u. | Excel Discussion (Misc queries) | |||
HOW CAN I TYPE MULTIPLE LINES IN A CELL | Setting up and Configuration of Excel | |||
vlookup with multiple lines of same value | Excel Worksheet Functions |