Excel 2013: How to copy selective columns form different workbook

I have two workbooks;

1)"PIN CODES": - Columns are, A)Pincode B)District C) State

2)"Customers Details": - Columns are, D)Pincode E)District f)State

If a pincode in a cell of D(workbook2) matches to the pincode of any cell of A(Workbook1), the respective cell values in B&C should be copied to E&F.

Can anyone help me to find a formula for that??

Thanks in advance!!!

Solution

From what I understand, you have a table like this in a workbook "PIN CODES" (top left is A1):

Pin Code    District      State
1           Houston         TX
2           LA              CA
3           Seattle         WA
4           Billings        MO
5           San Francisco   SF
5           Omaha           NE

and your second workbook, "Customers Details" has the same table, with only pin codes. You want to fill in this table based off the "PIN CODES" workbook.

In E1 of "Customers Details", you can use this Index/Match formula:

=INDEX('[PIN CODES]Sheet1'!B$2:B$7,MATCH($D2,'[PIN CODES]Sheet1'!$A$2:$A$7,0))

Note: Adjust the ranges' rows' from B2:B7 to whatever you need. You can drag that into F1 and it'll return the State match.

Finally, you can wrap an IfError() around that to return a blank (or whatever you want):

=IFERROR(INDEX('[PIN CODES]Sheet1'!B$2:B$7,MATCH($D2,['PIN CODES]Sheet1'!$A$2:$A$7,0)),"No Match")

Edit: And just for fun, if you wanted to be able to change the header row in your "Customers Details" worksheet and have the info update based on that, you can use this formula: =IFERROR(INDEX('[PIN CODES]Sheet1'!$A$2:$C$7,MATCH($D2,'PIN CODES]Sheet1'!$A$2:$A$7,0),MATCH(E$1,'[PIN CODES]Sheet1'!$A$1:$C$1,0)),"")