How do I match and combine data using VLookup?

Hi there, may I ask for help for a VLOOKUP question please? Sheet1 is way larger than Sheet2. I have simplified the question as follows. I need to find out the names in column A in Sheet1from Sheet2, and combine the other columns (Age, Gender, State, Zip Code) under the same names in Sheet3.

Anyone can help me please? Thank you so much!

Sheet1
A B C
NAME AGE GENDER
JOHN 40 M
SEAN 32 M
ERIC 24 M
LISA 56 F
MINDY 28 F
LEO 34 M
TONY 41 M
JEAN 26 F
LANCE 36 M

Sheet2
A B C
Name ZIP CODE STATE
MICHAEL 36295 MI
SEAN 37594 OH
ERIC 83620 CA
LINSEY 36422 NY
JOHN 46327 WA

Originally Posted by Glenn Kennedy

Is this what you meant?

Thank you for the reply! In Sheet 3, I only need names that are shown in both Sheet1 and Sheet2. How do I wrote the function?

Originally Posted by Glenn Kennedy

OK. Here you are (v2).

the formula to return the list of names in common is an array formula.

These are Array Formulas. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

Thank you so much for your prompt reply and detailed explanation. I don't have access to my computer right now. Just want to conform the fomulars are in the excel document you attached right?

Originally Posted by Glenn Kennedy

That's correct. Everything's in the attachment.

Thanks Glenn! I applied your formula to my large data base, but column A won't auto refill. How do I auto fill the entire column A without manually entering the formula?

Originally Posted by Glenn Kennedy

I'm not sure what you mean... However, the formulae that I used are ONLY in the green cells. Drag them down further, if needed. Also, the formulas are ALLset to look down both sheets as far as row 100. If that isn't enough - change the values to suit in row 2 and rdrag down. Don't forget, though, that the formula in A is an array formula and MUST be set with CTRL SHIFT ENTER.

I appreciate your reply. I typed your formulas in sheet3 of my huge database, but when I drag down column A in sheet3, the formulas don't apply to new rows; they only repeated the first several rows. I don't know what the problem is. But I don't have problems with the other B, C, D, E, F columns. Could you please revise the formulas in column A to include sheet1 to 80,000 rows and sheet2 to 10,000 rows? Then I can apply your formula directly without typing the formulas on my own. Thank you so much for your help!

Originally Posted by Glenn Kennedy

Sure. But before I do, please tell me which row the DATA (not the headers) start in sheets 1 and 2 in your real sheet.

Thanks! Column A in sheet3 is from Sheet1 A2 to A80,000, Sheet2 A2 to A10,000.

I figured it out. Thanks for helping, Glenn!