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?


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
ivan rosaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Pretty sure that you can do that with VLookup.
Just type:
=VLOOKUP in a cell and use the formula button to help build your expression
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Might be a column offset. Try using 1 or 2?
Table array should be a single column
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
for Sheet1's Column B (cell B1), add:

Open in new window

for Sheet1's Column C (cell C1), add:

Open in new window

for Sheet1's Column D (cell D1), add:

Open in new window

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
@Ryan  - parameter 2 should be A:A   not  A:D ?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:

>>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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
sample as attached.
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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:

no worries cheers
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
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...
byundtMechanical EngineerCommented:
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.

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

ivan rosaAuthor Commented:
thanks for you guys comment, this help me to think outside of the box, and apply it in my lab...
ivan rosaAuthor Commented:
this guy really knows this deal
Hi Byundt - thanks for the explanation ☺
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.