+ Reply to Thread
Results 1 to 22 of 22

Look up a value & return multiple values

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Unhappy Look up a value & return multiple values

    On Sheet 1 I have a list of "product codes" (Column C) and a corresponding "sale date" for those products (Column D). There are multiple occurrences of the same product codes in the list in column C, with different sale dates:

    eg:
    Product Sale date
    code
    387253 29/04/2013
    491960 14/05/2013
    491960 15/05/2013
    491960 16/05/2013
    536273 14/06/2013
    571790 03/05/2013
    641092 30/05/2013
    909264 20/05/2013
    909264 21/05/2013

    On Sheet 2, I have a list of "product codes" (Column A) and in the adjacent column (column B) a list headed "date received". There are multiple occurrences of the "date received" for each product code.

    eg:


    Product Date received
    code
    4843 03/07/2013
    4990 24/07/2013
    5122 03/07/2013
    38484 03/07/2013
    38484 24/07/2013
    38626 24/07/2013
    40615 03/07/2013
    45207 16/07/2013
    45207 17/07/2013
    57476 16/07/2013
    57501 16/07/2013
    60494 16/07/2013


    I want to lookup a "product code" on Sheet 1 and return the corresponding, multiple "dates received" against that product code from Sheet 2 - with the results in Sheet 1, Column E, if possible.

    I originally had the data in two separate pivot tables, but have taken to putting all the data in simple worksheet format and trying a look-up - but I dont know 1)how to return multiple values against each product code, and 2)from another work-sheet.

    My brain is scrambled. Looked all over the internet, so confused.

    Any solution? Thanks
    Last edited by FatKidonaTrampolin; 07-30-2013 at 09:19 AM. Reason: Mistake

  2. #2
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Look up a value & return multiple values

    On Sheet 1 I have a list of "product codes" (Column C) and a corresponding "sale date" for those products (Column D). There are multiple occurrences of the same product codes in the list in column C, with different sale dates:

    eg:
    Product Sale date
    code
    387253 29/04/2013
    491960 14/05/2013
    491960 15/05/2013
    491960 16/05/2013
    536273 14/06/2013
    571790 03/05/2013
    641092 30/05/2013
    909264 20/05/2013
    909264 21/05/2013

    On Sheet 2, I have a list of "product codes" (Column A) and in the adjacent column (column B) a list headed "date received". There are multiple occurrences of the "date received" for each product code.

    eg:


    Product Date received
    code
    4843 03/07/2013
    4990 24/07/2013
    5122 03/07/2013
    38484 03/07/2013
    38484 24/07/2013
    38626 24/07/2013
    40615 03/07/2013
    45207 16/07/2013
    45207 17/07/2013
    57476 16/07/2013
    57501 16/07/2013
    60494 16/07/2013


    I want to lookup a "product code" on Sheet 1 and return the corresponding, multiple "dates received" against that product code from Sheet 2 - with the results in Sheet 1, Column E, if possible.

    I originally had the data in two separate pivot tables, but have taken to putting all the data in simple worksheet format and trying a look-up - but I dont know 1)how to return multiple values against each product code, and 2)from another work-sheet.

    My brain is scrambled. Looked all over the internet, so confused.

    Any solution? Thanks
    Attached Files Attached Files
    Last edited by FatKidonaTrampolin; 07-30-2013 at 10:07 AM.

  3. #3
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Look up a value & return multiple values

    Does this work for you? (You'll need to change the sheet names to your own

    This needs to be entered with Ctrl Shift Enter

    =IFERROR(INDEX(Sheet1!$D$1:$D$12,SMALL(IF(Sheet1!$C$1:$C$12=Sheet2!A2,ROW($A$2:$A$10)-ROW($A$2)+1),COUNTIF($A$2:A2,A2))),"")
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  4. #4
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Hi!
    Thank you so much from replying.

    I've attached a copy of my spreadsheet.
    Im looking up the product code in green, against the product code on sheet 2, looking up against column B on sheet 2. There will be multiple values in Columm B on sheet 2.
    I'd like to have the various returns in Column e, sheet 1.
    Attached Files Attached Files

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Look up a value & return multiple values

    Cell C2 on Sheet 1 and copy down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    This assumes that the Product code is in column A on both sheets and the dates are on both sheets.

    In the example above, you appear not to have any matching data so I may be missing something in your description.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Look up a value & return multiple values

    Have you tried the solution from TheCman?

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Look up a value & return multiple values


  8. #8
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Hi
    Thanks for replying.

    I get a return, but only a single value, where there are multiple values in Sheet2

    eg. Code 123456 on Sheet 1, looking up 123456 on Sheet 2.

    On sheet 2 against 123456 there are multiple results (dates) .

    Not sure Im being clear, or am looking to do the impossible in it's current format

    Best

    PDM

  9. #9
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Yes, thanks and have replied.
    N
    ew to site, so thought I may have posted my original post in incorrect forum area - hence duplicate posts.

  10. #10
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Look up a value & return multiple values

    I've updated your sheet

    Inbound%20v%20sales%20Trial(1).xls

  11. #11
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Thanks for replying.

    I get a return, but only a single value, where there are multiple values in Sheet2

    eg. Code 123456 on Sheet 1, looking up 123456 on Sheet 2.

    On sheet 2 against 123456 there are multiple results (dates) .

    Not sure Im being clear, or am looking to do the impossible in it's current format

    Best

    PDM

  12. #12
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Look up a value & return multiple values

    Quote Originally Posted by FatKidonaTrampolin View Post
    Thanks for replying.

    I get a return, but only a single value, where there are multiple values in Sheet2

    eg. Code 123456 on Sheet 1, looking up 123456 on Sheet 2.

    On sheet 2 against 123456 there are multiple results (dates) .

    Not sure Im being clear, or am looking to do the impossible in it's current format

    Best

    PDM
    Did you review the attached spreadsheet in post 6?

    Also it's an array formula so it needs to be entered with Ctrl Shift Enter - you will see curly brackets at either side of the formula {}

  13. #13
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Hi,

    Yes, I used your spreadsheet and also entered using Ctrl Shift Enter.

    I get a return on the lookup, but only one value. How would I return & display multiple values?

  14. #14
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Look up a value & return multiple values

    That formula will return multiple values if you have multiple lookup values ie if you have 2 lookup values in Sheet1 and 3 values in Sheet2 it will return the first 2 instances!

    Can you repost the spreadsheet and highlight an example of where it's failing?

    Thanks

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Look up a value & return multiple values

    Not sure Im being clear
    Probably not

    I made up a workbook with two worksheets based on the examples in your original post. The formula uses the product code on sheet 1 to return matching records on sheet 2. It works for me in the sample workbook that I created using your sample data.

    If you want a solution that works in your workbook with your data, it's a good idea to provide a sample workbook with some typical data and indicate (manually mock up) what you hope/expect to get back in any given situation.

    Seems you might have done that but our posts crossed.

    Cell E2 and drag down

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Regards, TMS

  16. #16
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Yeah, that's what I thought....unfortunately

    The lookup was working, but if there isn't a third occurrence of the first lookup value, then it wont return a third result. Understood.

    I was hoping for some technique/trick to achieve a multiple value return against maybe a single lookup value. I suspect in the single row, table format I have, this isnt possible...

    Really appreciate your help.

  17. #17
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Just a thought...is it possible to produce multiple returns against a single lookup value if the results are produced horizontally, rather than down?

  18. #18
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Look up a value & return multiple values

    QUOTE=FatKidonaTrampolin;3344443]Just a thought...is it possible to produce multiple returns against a single lookup value if the results are produced horizontally, rather than down?[/QUOTE]

    So something like this?

    Inbound%20v%20sales%20Trial(1).xls

  19. #19
    Registered User
    Join Date
    07-30-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2016
    Posts
    33

    Re: Look up a value & return multiple values

    Bl**dy hell.
    Holmes you've done it!! Perfect!
    I can perform horizontally and then paste/transpose to produce vertically.
    I'll try that tomorrow.

    Amazing. Thank you so much for your time. "Reputation" added

  20. #20
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Look up a value & return multiple values

    No problem glad to help and thanks for the feedback

  21. #21
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Look up a value & return multiple values

    @Pepe: thanks for the heads-up

    @FatKidonaTrampolin: please have the courtesy to acknowledge the replies. Be good if you could do that quickly before I get the Moderators to lock the thread.

    In future, if you are going to double- or cross-post, please let everyone know so that you won't waste quite as many people's time.

    TMS

  22. #22
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Look up a value & return multiple values

    Your duplicate posts did not comply with Rule 5 of our forum rules. These threads were merged so everyone could see what was going on. You certainly will waste someone's time by doing that, which is why it is against the rules.

    When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

    (link above in the menu bar)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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] Return value if within range, return multiple values if ranges overlap
    By cde1983 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-17-2013, 06:16 AM
  2. How to find closest values in multiple columns and return adjacent values.
    By Patrician in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 01:47 PM
  3. Return Multiple Values with multiple rows & multiple columns
    By sachin parab in forum Excel General
    Replies: 2
    Last Post: 11-05-2012, 10:21 AM
  4. Replies: 2
    Last Post: 07-17-2012, 11:53 AM
  5. [SOLVED] Search multiple values to return single values
    By JANA in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-27-2005, 12:05 PM

Tags for this Thread

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