Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 29 '13 at 12:46

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

add comment

2 Answers

up vote 1 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.

=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

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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.