+ Reply to Thread
Results 1 to 4 of 4

Lookup multiple values in a single cell (separated by commas) and then return the values

  1. #1
    Registered User
    Join Date
    12-12-2015
    Location
    Calirfornia
    MS-Off Ver
    2010
    Posts
    6

    Lookup multiple values in a single cell (separated by commas) and then return the values

    **XCEL FILE ATTACHED**

    Question: If I have a workbook with 2 tables; 1 has the data that I want to search and 1 is empty and where I would like the "searched" values output, what lookup formula would I need to use?.
    The attached file is a list of companies in one column and the states in which they have locations in the adjacent column
    Each company has multiple states where they have locations (For each company, these are separated by semi-colons For Ex: TX; CA; MA; NY)
    I want to search, for ex, MA and have it output all the different companies that have MA locations
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Lookup multiple values in a single cell (separated by commas) and then return the valu

    Would you be willing to use the built in filter tools?
    Autofilter to filter list in place http://www.wikihow.com/Use-AutoFilter-in-MS-Excel (select "state locations" dropdown, text filters, contains, MA)
    Advanced filter to send copy of filtered list to other area in same sheet http://www.contextures.com/xladvfilter01.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    12-12-2015
    Location
    Calirfornia
    MS-Off Ver
    2010
    Posts
    6

    Re: Lookup multiple values in a single cell (separated by commas) and then return the valu

    Quote Originally Posted by MrShorty View Post
    Would you be willing to use the built in filter tools?
    Autofilter to filter list in place http://www.wikihow.com/Use-AutoFilter-in-MS-Excel (select "state locations" dropdown, text filters, contains, MA)
    Advanced filter to send copy of filtered list to other area in same sheet http://www.contextures.com/xladvfilter01.html
    I want to be able to have a excel workbook set so that all i have to do is drop in/copy paste new companies & their respective state locations and have it output into the table. The list has 2000 companies and will grow over time so it would be too hard/not really scalable to do it that way.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Lookup multiple values in a single cell (separated by commas) and then return the valu

    Not certain I understand the logic of the "Company" cell in the output table. This is just a guess.

    Array enter this formula in F3 copy down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Row\Col
    E
    F
    G
    H
    I
    2
    State(s) of Interest MA CA NY NJ
    3
    Company
    Company 2 Company 1 Company 3 Company 3
    4
    Company 3 Company 3 Company 6 Company 4
    5
    Company 5 Company 4 Company 7 Company 6
    6
    Company 6 Company 5 Company 8 Company 7
    7
    Company 10 Company 6 Company 8
    8
    Company 8 Company 10
    9
    Company 9
    10
    Company 10
    11
    12



    Does this do what you want?
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to count values in single cell separated by commas
    By aimeecrystalaid in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-25-2017, 01:01 AM
  2. [SOLVED] Return multiple matchng VLOOKUP values in one cell separated by commas?
    By zendoo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-14-2015, 06:10 AM
  3. [SOLVED] vLookup multiple values separated by ";" in single cell / return result in single cell
    By Tunesmith in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2015, 12:02 PM
  4. Returning Multiple Values Separated by Commas to a single cell
    By enragedpigeon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2015, 05:48 PM
  5. Replies: 5
    Last Post: 07-07-2013, 03:39 PM
  6. Replies: 5
    Last Post: 06-05-2012, 03:32 PM
  7. [SOLVED] lookup multiple values of a single cell and return sum of results
    By Zeppelin17 in forum Excel General
    Replies: 7
    Last Post: 08-10-2011, 07:09 PM

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