Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a worksheet with a column containing names of Provinces and a column with its corresponding Region. On a separate worksheet I have an Address column. What I want to do is to find the Province in the given Address, then ultimately finding out what the corresponding region is.


On Sheet1

Client    | Address                | Region
Tony      | 5th Avenue, Manila     | ???
Grace     | Mactan, Cebu           | ???

On Sheet2

Region    | Province
NCR       | Caloocan
NCR       | Manila
Visayas   | Bohol
Visayas   | Cebu

I need to look for the values in the Province column on Sheet2 within the Address cell, i.e., search for "Caloocan" "Manila" etc. in "5th Avenue Manila", and when found - return the Region, i.e., NCR. Such that the end product is the following:

On Sheet1

Client    | Address                | Region
Tony      | 5th Avenue, Manila     | NCR
Grace     | Mactan, Cebu           | Visayas

Any input would be awesome. Thanks.

share|improve this question

migrated from Jan 29 '13 at 12:46

This question came from our site for professional and enthusiast programmers.

up vote 2 down vote accepted

Let's say your data looks like this

enter image description here

Try this formula. Enter this formula in Cell C2 of sheet1 and copy it down. Do not simply press Enter as this is an array formula. You have to press CTL + SHIFT + ENTER after you enter the formula.


So that it looks like this

enter image description here

share|improve this answer
Amazing! It works! Thank you! Now will this work if I have > 100 rows? And would it be too much if I ask you to explain your formula? Thanks again. – ome Jan 29 '13 at 2:09

Using Sid's layout you can use this formula in C2 copied down


If there is no match then that will give you an error #N/A

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .