Creating 'Region' column and auto filling data. Please help.

**leepwbriggs** · 06-12-2015, 04:38 AM

Hello,

New to the forum – thanks for having me!

I’d be super grateful if someone could help me with a solution to the following problem -

I’ve a set of personal data (UK). I have a column containing location - ‘County’ data - around 90 options (Essex, Lancashire, Merseyside etc). For around 100,000 rows of data – people.

We didn’t collect region data for each of the people – North West, North East, Greater London etc.

I’d like to add a region column and am looking for a solution to auto fill values rather than manually add across the 100,000 entries.

I have a separate worksheet with all 90 of the ‘counties’ in column A, and their corresponding region in column B.

example.PNG

I guess I’m looking for a formula for ‘Worksheet 1’ C2 (new region column), that does something like:

Look in Column B2 (county), if the value matches any of the values in Worksheet 2 Column A (county), Place the value that’s in the column next to it – B (region) - here.

Thanks,
Lee

**Sam Capricci** · 06-12-2015, 04:42 AM

it appears a VLOOKUP would work, something like =VLOOKUP(B2,Worksheet2!A:B,2,False)

**leepwbriggs** · 06-12-2015, 05:01 AM

Worked perfectly - thank you!

**Sam Capricci** · 06-12-2015, 05:07 AM

Great! And being new to the forum just some helpful suggestions.
Don't forget to mark your post as solved using the thread tools dropdown at the top of the post and you can thank any and all contributors by clicking on the "* Add Reputation" at the bottom of their posts.

**sakib** · 06-12-2015, 06:52 AM

Dear leepwbriggs,

Also you can try with the below formula:

=INDEX(Sheet2!B:B,MATCH(Sheet1!B:B,Sheet1!B:B,0))

Test in the attached file with new data input and you will see the result.

If this helps, then sending some reputation will be highly appreciated.

Thanks.

Sakib