Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I need to perform a kind of reverse lookup. I am going to have a huge list of addresses (see sheet 2) where the city/state/zip could show in any of 5 possible address lines. I want to perform a check (see columns F-J in sheet 2) that will give me a Y/N response if each of the corresponding address lines reflect a valid state. The states are listed in sheet 1. I've tried different variations of a FIND, SEARCH, and VLOOKUP. Each one of those is where you're looking for a specific thing in a whole list of data. I looking for the possiblity of a whole list of data within each single cell. Where am I getting stuck on how to do this?

    Thanks as always!
    Christine
    Attached Files Attached Files
    thanks
    christine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Enter the following array formula (confirm with Ctrl+Shift+Enter) in cell F2 on Sheet2:

    =IF(SUM(1*NOT(ISERROR(SEARCH(" "&Sheet1!$B$2:$B$60&" ",A2))))>0,"Y","")

    Fill right to J2, then (with F2:J2 selected) fill down as far as needed.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    This rocks...as usual! I really need to get better at arrays! Thanks again, Hans!!!!
    thanks
    christine

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote name='cchambers@work' post='779330' date='11-Jun-2009 10:29']I really need to get better at arrays![/quote]

    A couple excellent tutorials are by the MS MVPs Bob Umlas and Chip Pearson

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The key element in the formula is placing a space before and after the state name in " "&Sheet1!$B$2:$B$60&" ", so that the SEARCH function won't pick up two letter combinations in longer words. Without the spaces, SEARCH would find AL in CALIFORNIA and RI in FLORIDA.
    There's a small chance of "false positives", for example if an address line contains IN or OR as a word instead of a state abbreviation.

Posting Permissions

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