# Find occurrences of values in a given array in a single cell in Excel

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.

Example:

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.

-

## migrated from stackoverflow.comJan 29 '13 at 12:46

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

Let's say your data looks like this

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.

`=OFFSET(Sheet2!\$B\$1,MATCH(INDEX(Sheet2!\$B\$1:\$B\$5,MAX(IF(ISERROR(FIND(Sheet2!\$B\$1:\$B\$5,Sheet1!B3)),-1,1)*(ROW(Sheet2!\$B\$1:\$B\$5)-ROW(Sheet2!\$B\$1)+1))),Sheet2!\$B\$1:\$B\$5,0)-1,-1)`

So that it looks like this

-
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

`=LOOKUP(2^15,SEARCH(Sheet2!B\$2:B\$5,B2),Sheet2!A\$2:A\$5)`

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

-