+ Reply to Thread
Results 1 to 4 of 4

Returning multiple values from a single lookup

  1. #1
    Registered User
    Join Date
    11-03-2020
    Location
    Manchester
    MS-Off Ver
    MS Excel 365
    Posts
    44

    Returning multiple values from a single lookup

    Hi Guys

    I am looking for a way to return multiple items under a product.

    for example, if I do a vlookup on product to return the items associated with that product the formula only brings back the first match and not the rest of the items.

    Please see attached.

    On the attached sheet you will find two tables. On the left-hand (sheet 1), is the result I want to be displayed after manually entering it. Sheet 2 is the the DB to do the lookup from.

    This is a small example of the data I am working and wondering is there an automated way I can do this?.

    Many thanks for your support in advance

    MH Hersi
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-03-2021
    Location
    Nashville
    MS-Off Ver
    MS Office 365
    Posts
    184

    Re: Returning multiple values from a single lookup

    I am assuming "001ABT-T4" is the Product and "EAFE02775401" is the item?

    Are you trying to only one list at a time so if you lookup "001ABT-T4" You return all values (Items) under that Product?

    This really seems like a Pivot Table issue. This would take care of the Single Product and then List all "Items" under that product.
    No real formula needed. (took out the merged cell, pivot tables hate merged cells).
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Returning multiple values from a single lookup

    Sheet1

    A1=IFERROR(INDEX(Sheet2!$A$1:$A$100,SMALL(IF(FREQUENCY(IF(Sheet2!$A$1:$A$100<>"",MATCH(Sheet2!$A$1:$A$100,Sheet2!$A$1:$A$100,0)),ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),ROW(Sheet2!$A$1:$A$100)-ROW(Sheet2!$A$1)+1),COLUMNS(Sheet1!$A$1:A1))),"")

    Control+shift+enter

    copy across

    A2=IF(A$1<>"",IFERROR(INDEX(Sheet2!$B$1:$B$100,AGGREGATE(15,6,ROW(Sheet2!$B$1:$B$100)-ROW(Sheet2!$A$1)+1/(Sheet2!$A$1:$A$100=Sheet1!A$1),ROWS(Sheet2!$A$1:A1))),""),"")

    Copy across and down
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-03-2020
    Location
    Manchester
    MS-Off Ver
    MS Excel 365
    Posts
    44

    Re: Returning multiple values from a single lookup

    Hi All,

    I know i've posted this thread a while ago, but i just wanted to say thanks for the support. you input does really help

+ 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. Vlookup returning multiple values in one single cell
    By Mona07 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-17-2017, 04:28 AM
  2. Replies: 2
    Last Post: 04-30-2014, 05:59 AM
  3. [SOLVED] VLookup - Single value lookup returning multiple records into multiple columns
    By kllovin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-07-2014, 05:14 AM
  4. Checking for single value, returning Multiple values
    By English_Bloke82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2013, 01:31 PM
  5. Returning a value to a single cell based on multiple values elsewhere.
    By archieross in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2012, 07:35 PM
  6. Returning a multiple-result lookup to a single cell
    By shockeroo in forum Excel General
    Replies: 2
    Last Post: 04-17-2009, 03:54 PM
  7. Replies: 3
    Last Post: 10-10-2005, 01:05 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