Match city and paste address and post code

[amitvnarendra]

Hello,

I have a list of cities in sheet 1. I have another sheet, lets call it sheet 2 which has got cities with address and post code.
In sheet 1 next to city column, I want to look for cities in sheet 2 and if the city matches then I want to copy the address and post code in Column B and C respectively in sheet 1.
Is there a quick way to perform this task?

Any help on this topic will be highly appreciated.

Kind regards,

Amit Narendra

 

[SanjayGMusafir]

Hello,

I have a list of cities in sheet 1. I have another sheet, lets call it sheet 2 which has got cities with address and post code.
In sheet 1 next to city column, I want to look for cities in sheet 2 and if the city matches then I want to copy the address and post code in Column B and C respectively in sheet 1.
Is there a quick way to perform this task?

Any help on this topic will be highly appreciated.

Kind regards,

Amit Narendra

I don't know what version of excel and platform you are using - You can update same here and in your profile

But what you want to do can be done through VLOOKUP or XLOOKUP depending on your version of excel.

 

[amitvnarendra]

Hi, can you please provide the formula for the Vlookup function? I know it can be done through vlookup but I need step by step explaination

 

[SanjayGMusafir]

Hi, can you please provide the formula for the Vlookup function? I know it can be done through vlookup but I need step by step explaination

for that need your version of excel, platform and a sample data uploaded through XL2BB utility. Need to see how data looks like on sheet 2.

 

[amitvnarendra]

Hello,

Thank you for your prompt response.
I am using Office 365.
Please see below the two screen shots.

So from screen shot 1, I want Column C to match with Column C in screen shot 2. If it matches then I want address in Column D and Post Code in Column H of screen shot 2 to be copied over in Column E and Column F of screen shot 1

 

[SanjayGMusafir]

Hello,

Thank you for your prompt response.
I am using Office 365.
Please see below the two screen shots.

So from screen shot 1, I want Column C to match with Column C in screen shot 2. If it matches then I want address in Column D and Post Code in Column H of screen shot 2 to be copied over in Column E and Column F of screen shot 1

View attachment 72172

View attachment 72173

Try this -

With Excel 365 XLOOKUP is a better option - Because XLookup is not dependent on column order.

for PIN

=XLOOKUP(C2,Sheet2!C:C,Sheet2!H:H,"Not Found",0)

for Address

=XLOOKUP(C2,Sheet2!C:C,Sheet2!D:D,"Not Found",0)

Also update your version in your profile, so that it automatically appears.

 

[Fluff]

I am using Office 365.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[amitvnarendra]

Try this -

With Excel 365 XLOOKUP is a better option - Because XLookup is not dependent on column order.

for PIN

=XLOOKUP(C2,Sheet2!C:C,Sheet2!H:H,"Not Found",0)

for Address

=XLOOKUP(C2,Sheet2!C:C,Sheet2!D:D,"Not Found",0)

Also update your version in your profile, so that it automatically appears.

Thank you Sanjay. However the formula is just returning Not Found data. So not sure what the problem is.

 

[SanjayGMusafir]

Thank you Sanjay. However the formula is just returning Not Found data. So not sure what the problem is.

"Not Found" Was kept just in case Exact match is not found.

And if it is giving same message, this means cell referencing is not correct, in terms of worksheet name, cells to look for - These are the common problems with XLOOKUP

That you have to manually identify as per your sheets and other things.

 

[SanjayGMusafir]

Thank you Sanjay. However the formula is just returning Not Found data. So not sure what the problem is.

Another way of checking any error if formula is using Formula Builder that you can get by clicking Fx in formula bar see attached image.