+ Reply to Thread
Results 1 to 3 of 3

Help, lookup multiple values within a column

Hybrid View

  1. #1
    Registered User
    Join Date
    04-13-2005
    Posts
    2

    Exclamation Help, lookup multiple values within a column

    Here is my situation, hopefully someone can help. I have a data set which sorts potential employees by which county they would like to work in. A person may mark multiple counties.

    Name County
    Datthyn 06,18,29
    Moore 26
    Sloan 06,18
    Walker 06,18,29
    Visarraga 06,18
    Tiek 06,18,25,29
    Hansen 06,18,29
    Dawes 06,18,25
    Stewart 06,18,25,29
    Washington 06,29
    Shipley 06,29

    What I want to do is be able to create a formula that I can use to tell me if a person has marked certain counties. For example I would like it to tell me if person has selected both 06 and 18 then in the following column tell me "true" or "False" or something similar. I hope this makes sense. I am fairly experienced with Excel but no math wiz. If anyone can help please do!

    Jay jws34@hotmail.com

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    I think I'd try Excel's Advance Filter for this one.

    Assumption: Your list is in Cells A1:B??

    Try this:
    E1: Country
    E2: *06*

    F1: Country
    F2: *18*

    I1: Name
    J1: Country

    Next: Select your data list
    Then: Data>Filter>Advanced Filter
    Criteria: $E$1:$F$2
    Check "Copy to another location"
    Copy To: $I$1:$J$1

    Click OK

    All potential employees who selected 06 AND 18 wil be listed under cells $I$1:$J$1.

    Does that give you something to work with?

    Regards,
    Ron

  3. #3
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Ron's solution will filter your values and give you your information. Nice tip, Ron.

    If, however, you want a column along side of your data, in Column C enter this formula and copy down...

    =IF(ISERROR(AND(FIND("06",B2),FIND("18",B2))),"FALSE","TRUE")

    You can add more (or less) "FIND" statements within the AND statement if need be. Of course, adjust ranges as necessary.

    HTH

    Bruce
    Bruce
    The older I get, the better I used to be.
    USA

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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