+ Reply to Thread
Results 1 to 4 of 4

Using INDEX and MATCH to return unique data in corresponding columns into drop down list

  1. #1
    Registered User
    Join Date
    06-17-2019
    Location
    Middlesbrough
    MS-Off Ver
    2011
    Posts
    2

    Using INDEX and MATCH to return unique data in corresponding columns into drop down list

    Hi All,

    I am trying to search for all matching values in column A and return the corresponding values in column B but I want the values returned from column B in a data validation drop down list.

    I have attached a simplified table of the selected data I am using.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Using INDEX and MATCH to return unique data in corresponding columns into drop down li

    Hello carlholmstrom. Welcome to the forum.

    Try defining the list in a helper column. Try this formula in column L and fill down to get blanks a little beyond number of Serial Numbers anticipated.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then used this for the DV definition.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 06-17-2019 at 08:16 PM.
    Dave

  3. #3
    Registered User
    Join Date
    06-17-2019
    Location
    Middlesbrough
    MS-Off Ver
    2011
    Posts
    2

    Re: Using INDEX and MATCH to return unique data in corresponding columns into drop down li

    Thanks FlameRetired, that works!

    Can those formulas be used without helper cells? Also is it possible to sort DV lists?

    Cheers!

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Using INDEX and MATCH to return unique data in corresponding columns into drop down li

    Can those formulas be used without helper cells?
    I misspoke. That is not a helper column. My bad. It is the DV list. The DV 'Source:' requires it.

    If it cannot be typed directly into the 'Source:' dialogue box the DV definition must reference a range. This can be done by range address or by a range defined by formula such as the OFFSET formula I proposed. DV manager accepts either one.

    DV list column can be hidden for aesthetic purposes and still work or it can be located out of sight (to the extreme right?) or even on another sheet.

    Also is it possible to sort DV lists?
    To sort the resulting DV list replace that first formula with this sorting formula found in column L.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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] Cant get INDEX, MATCH to return more than one unique value.
    By codemancode in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2019, 10:30 AM
  2. Index/Match return all unique values
    By eggselent in forum Excel General
    Replies: 1
    Last Post: 10-02-2017, 12:13 PM
  3. Return an unique list of data that match criteria
    By tantcu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-03-2017, 04:02 PM
  4. [SOLVED] Using INDEX MATCH to return unique values for non-unique search term
    By rico_suave in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-03-2015, 01:53 AM
  5. Lookup using Index & Match to Return Unique Output
    By apdiego in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2013, 03:45 AM
  6. Replies: 2
    Last Post: 02-02-2013, 05:24 PM
  7. Return column of data by selecting index value from drop down list
    By DWJ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2010, 12:45 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