# Return text value with lookup based on multiple criteria?

1. ## Return text value with lookup based on multiple criteria?

Can anyone help me with this? I could swear I know how to do this/have done it before, but cannot seem to make it work.

Basically I have two worksheets with data. The first has sales figures based on City and State, but does not show the district (this is the info I am looking for) and I was not provided with the zip codes to pull in the district off just that one field (unless someone instead tells me how to retrieve all the zip code data easily (thousands of zip codes).

The second worksheet is a list of the districts and City, States that they represent. Basically I need to pull the District from worksheet 2 into worksheet 1 based on the City and State matching up. See below. Any ideas?

Thanks!

Worksheet 1
Sales Rank(A)- City (B) - State (C) - District? (D)
1 - Stockbridge - GA - {Formula to be entered here}
2 - Milton - MA - {Formula to be entered here}
3 - Lawrenceville - NJ - {Formula to be entered here}
4 - Marietta - PA - {Formula to be entered here}
5 - Marrietta - GA - {Formula to be entered here}

Worksheet 2
City (A) - State (B) - District (C)
Smyrna - DE - MD3
Milton - MA - NE1
Milton - VT - NE4
Lawrenceville - NJ - NJ4
Gainesville - NY - NY1
Marietta - PA - PA01
Marrietta - GA - SGA12
Stockbridge - GA - SGA14

2. ## Re: Return text value with lookup based on multiple criteria?

well assuming those hyphens indicate that they're separate columns,

I would insert a helper column between state and district in worksheet 2, such that:
A1 = city_name
B1 = State
C1 = A1&B1
D1 = district_code

So the helper column is just the city and state concatenated together. It would look ugly, but it lets us run a straightforward VLOOKUP over on the first sheet.

``Please Login or Register  to view this content.``
You might have to change which cells it's pointing at, but this should do it.

3. ## Re: Return text value with lookup based on multiple criteria?

use an index/match
=INDEX(Sheet2!\$C\$1:\$C\$200,MATCH(B2&C2,INDEX(Sheet2!\$A\$1:\$A\$200&Sheet2!\$B\$1:\$B\$200,0),0))

4. ## Re: Return text value with lookup based on multiple criteria?

Thank you both! I feel kind of dumb for not thinking of the concatenating/vlookup option since I have already used both formulas elsewhere in this spreadsheet! But I know I have done it (I thought with an Index/Match formula) before in a single cell; so that's really what I was looking for. But that was a great quick fix!

----------------------------

use an index/match
=INDEX(Sheet2!\$C\$1:\$C\$200,MATCH(B2&C2,INDEX(Sheet2!\$A\$1:\$A\$200&Sheet2!\$B\$1:\$B\$200,0),0))
This is exactly what I was trying to do, but couldn't make it work (it does now - thanks)! Apparently I don't really understand the formula though. Would you mind breaking it down for me?

Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)