+ Reply to Thread
Results 1 to 4 of 4

Return text value with lookup based on multiple criteria?

  1. #1
    Registered User
    Join Date
    11-01-2011
    Location
    Rhode Island, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    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. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    11-01-2011
    Location
    Rhode Island, USA
    MS-Off Ver
    Excel 2010
    Posts
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1