Vlookup, adding a new column and adding a new sheet

[gavinloi]

Hi all,

I am having some issues here and hope that you can help.

I would like to do (1) a vlookup between 2 difference excels, (2) creating a new column for the results of the vlookup and (3) pasting the data from the 2nd excel into a new sheet in the first excel.

The vlookup will query from the 2nd excel and populate the results in the 1st excel in a new column that would be created. once that is done, the data would also be pasted in the 1st excel in a new sheet.

Would this be possible ?

Thank you!

Test file A

Country	Population
USA	4000
Spain	700
Hong Kong	10
Singapore	8
Portugal	500
China	300
Japan	100

Test file B

Male	Female	Country
2000	2000	USA
400	300	Spain
4	6	Hong Kong
5	3	Singapore
200	300	Portugal

Expected output

Country	Vlookup	Population
USA		4000
Spain		700
Hong Kong		10
Singapore		8
Portugal		500
China		300
Japan		100

Thank you!

Gavin

[Qiu]

@gavinloi 
Can you check your expected output sample again?

Can not see how is the Vlookup working here. 😅

[Kurohits · Accepted solution]

Based on your question, and as per my understanding..I have came up with this workflow. Check it out if this helps. 

[gavinloi]

Apologies, the expected output should be as below.

the vlookup would take "countries" from both excels and if there is a match it shows the "country" or if not it would be NA.

Expected output

Country	Vlookup	Population
USA	USA	4000
Spain	Spain	700
Hong Kong	Hong Kong	10
Singapore	Spain	8
Portugal	Hong Kong	500
China	NA	300
Japan	NA	100

[Qiu · Accepted solution]

@gavinloi 

I still have doubt about the your output but perhaps like this.

[gavinloi]

Thanks @Kurohits and @Qiu 

Have used both methods and twigged abit to get the desired solution!

Many thanks and have accepted both as solutions! 🙂

[Qiu]

@gavinloi 
Glad to help and thank you for the accept mark. 😁