Pull Rows from a cell

Hello Colleagues,

Is it possible to create the following
my targets would be for example in column A, and the pool of records is in sheet 2, let's say if I was trying t pull the records I have for that person and put their entire rows next to their name automatically is that possible?

eg.

sheet 1    example on sheet 1Column A      Column B      Column C       Column D
John doe

At Sheet2 example on sheet 2john doe   32 years old      Male    email

so it would automatically search it and paste it together like so
sheet 1 (final product) example on sheet 1 once you input the dataColumn A      Column B      Column C     Column D
John doe       32 years old      Male           email
LVL 3
ivan rosaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrTribosCommented:
Pretty sure that you can do that with VLookup.
0
DrTribosCommented:
Just type:
=VLOOKUP in a cell and use the formula button to help build your expression
0
ivan rosaAuthor Commented:
hmm, perhaps we are getting very close on the answer however it gives me an error err1.jpg
what would you say I'm missing?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DrTribosCommented:
Might be a column offset. Try using 1 or 2?
0
DrTribosCommented:
Table array should be a single column
0
Ryan ChongCommented:
for Sheet1's Column B (cell B1), add:
=VLOOKUP(Sheet1!A1,Sheet2!A:D,2,FALSE)

Open in new window

for Sheet1's Column C (cell C1), add:
=VLOOKUP(Sheet1!A1,Sheet2!A:D,3,FALSE)

Open in new window

for Sheet1's Column D (cell D1), add:
=VLOOKUP(Sheet1!A1,Sheet2!A:D,4,FALSE)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrTribosCommented:
@Ryan  - parameter 2 should be A:A   not  A:D ?
0
Ryan ChongCommented:
@DrTribos

>>parameter 2 should be A:A   not  A:D ?

since we are lookup for columns: Column B,  Column C, Column D in Sheet2, we need to use range A:D instead.
0
Ryan ChongCommented:
sample as attached.
28733360.xlsx
0
DrTribosCommented:
Hi Ryan - thanks, yes you are right :-D I'm a bit rusty in XL.  

Ivan, also, as per Ryan's example, RangeLookup should be false - requires an exact match.
0
Ryan ChongCommented:
@DrTribos

no worries cheers
0
ivan rosaAuthor Commented:
thank you both, so far, Ryan's formula seems to be working I'll apply it tomorrow in my lab environment and see if there any additional changes I might need to do, and of course close the ticket
0
DrTribosCommented:
Ivan - my quick test, the only way I get #N/A is if the lookup name does not exist in the lookup column...

You might be able to do some data conditioning to remove extra spaces, stray caps etc., e.g.:

Orig (A) |  Cond (B)
Ivan |  =lcase(trim(A1))

...then do lookup on the conditioned data...

poorly explained, hope you get my drift...
0
byundtCommented:
DrTribos,
The Asker got the #N/A error in his first attempt with VLOOKUP because the fourth parameter was TRUE. This means that Sheet2 column A must be sorted in alphabetical order. Since the first name in Sheet2 was "michelle smith", Excel gives up looking for "john doe" as j occurs before m in the alphabet.

The right way to use the VLOOKUP for this problem, as Ryan Chong pointed out, is to use FALSE as the fourth parameter of VLOOKUP. If there is a possibility that "john doe" might not be on the list on Sheet2, you can wrap the VLOOKUP inside an IFERROR. The following formula returns an empty string (looks like a blank) if "john doe" is not found.
=IFERROR(VLOOKUP(A2,Sheet2!$A$1:$D$1000,3,FALSE),"")

I used $ in the table address part of the formula so you could copy it down and across without needing to update the address.

Brad
1
ivan rosaAuthor Commented:
thanks for you guys comment, this help me to think outside of the box, and apply it in my lab...
0
ivan rosaAuthor Commented:
this guy really knows this deal
0
DrTribosCommented:
Hi Byundt - thanks for the explanation ☺
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.