+ Reply to Thread
Results 1 to 26 of 26

Lookup and return multiple values multiple cells

  1. #1
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Lookup and return multiple values multiple cells

    Hi,

    I have a workbook with two sheets.

    On the first sheet, I'd like to have columns from Program(Column M) to Conditions Notes(Column T) to look at the value in HS Code(Column I) and return multiple values if it matches against the HS and data on the second sheet.

    Cross post link.
    reddit.com/r/excel/comments/bjlx8t/lookup_and_return_multiple_values_multiple_cells/
    Last edited by dominicb; 06-16-2019 at 04:33 AM. Reason: Crosspost link (posted by the OP) re-instated

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Lookup and return multiple values multiple cells

    In Excel2010 you don't have TEXTJOIN, use this UDF then:

    Please Login or Register  to view this content.
    and put into M2:

    Please Login or Register  to view this content.
    accept it with Ctrl+Shift+Enter (not just Enter).

    you can drag it right BUT you have to edit correctly 'bolded' range in above to be sure that data is grabbed from proper column on PGA sheet (I've set data from C into col. M).
    Then drag everything down.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    UDF
    Use in OVS Data sheet

    M2: =VLookUps($I2,'PGA Data'!$A1:C$15150,3,", ")
    N2: =VLookUps($I2,'PGA Data'!$A1:B$15150,2,", ")
    O2: =VLookUps($I2,'PGA Data'!$A1:E$15150,5,", ")

    P2: =VLookUps($I2,'PGA Data'!$A1:G$15150,COLUMN(G1),", ")
    fill P2 up to T2, then fill M2:T2 down.

    To a standard module
    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: Lookup and return multiple values multiple cells

    Or this in a new module.
    Please Login or Register  to view this content.
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

  5. #5
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Yes!! This is exactly what I was hoping for, thanks!

    Couple of questions, is there a way to edit the code to exclude the zero values?

    Something wonky is going on with "Match If" column P, it's only returning two values as opposed to three? It should be N, Y, Y.

  6. #6
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: Lookup and return multiple values multiple cells

    Yes!! This is exactly what I was hoping for, thanks!
    Which solution are you referring to?

  7. #7
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    The zero values appearing in a few columns.
    Attachment 623142

    This code should have returned 3 values, N / Y / Y, instead there are only two.
    Attachment 623143

  8. #8
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862

    Re: Lookup and return multiple values multiple cells

    Attachments are invalid / can't open them and which solution are you referring to?

  9. #9
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by KOKOSEK View Post
    In Excel2010 you don't have TEXTJOIN, use this UDF then:

    you can drag it right BUT you have to edit correctly 'bolded' range in above to be sure that data is grabbed from proper column on PGA sheet (I've set data from C into col. M).
    Then drag everything down.
    I uploaded the images to imgur since I can't post screenshots or links.

    How do I hide the zero values?

    /NVbHwyy

    Column P is only returning two values, when it should be three.
    /FcPAqrF

  10. #10
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by jindon View Post
    UDF
    Use in OVS Data sheet

    M2: =VLookUps($I2,'PGA Data'!$A1:C$15150,3,", ")
    N2: =VLookUps($I2,'PGA Data'!$A1:B$15150,2,", ")
    O2: =VLookUps($I2,'PGA Data'!$A1:E$15150,5,", ")

    P2: =VLookUps($I2,'PGA Data'!$A1:G$15150,COLUMN(G1),", ")
    fill P2 up to T2, then fill M2:T2 down.

    To a standard module
    Please Login or Register  to view this content.
    This formula also works great!

    Is there a way to be able to drag the formula down? It seems to cause the range to change in each cell.

    For instance row 5 has the range start at =VLookUps($I5,'PGA Data'!$A4:C$15150,3,", ")

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    Missed $,
    All $A1 should be $A$1.

    Exlude 0
    Please Login or Register  to view this content.
    When bulk data, UDF gets slower so, here's another sub routine
    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 06-16-2019 at 09:06 PM. Reason: Attachment with sensitive data removed

  12. #12
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Lookup and return multiple values multiple cells

    Last parameter in textjoinSUB (true,false or 1,0) is parameter which decide that value have to be unique.
    If you get 2 in col. P probably one of them as duplicate, if you want to see it, change 1 into 0.

  13. #13
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by jindon View Post
    Missed $,
    All $A1 should be $A$1.

    When bulk data, UDF gets slower so, here's another sub routine

    [/code]
    Awesome, thanks!

    Can you give a summary of how the sub routine works? What it does?

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    1) Store related columns of data for both sheet in array a & b to speed up the process.
    2) Process and put all matched data in one element.
    3) Loop through all the columns and delete duplicate using Dictionary object and dump the data in one go.

    HTH.

  15. #15
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by jindon View Post
    1) Store related columns of data for both sheet in array a & b to speed up the process.
    2) Process and put all matched data in one element.
    3) Loop through all the columns and delete duplicate using Dictionary object and dump the data in one go.

    HTH.
    Thank you!

    I changed the column reference to absolute from P to T, when I was dragging them down it was shifting the column number. Column P is also only returning unique values, how can it be changed to return all values? Some combinations might have multiple "N" values that need to show up.

    On the third sheet I've attached an example of data is copy/pasted into column A to I, the data always maxes out at 999. On my pc, with 4 processors running it takes less than a minute. On my work pc with two processors it takes up to 5 minutes.

    Do you mind taking one last look at the spreadsheet, see if anything stands out or can be changed to make it process quicker? You can ignore columns U and onward, I may or may not need them in the future.
    Last edited by 6StringJazzer; 06-16-2019 at 09:07 PM. Reason: Attachment with sensitive data removed

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    OK, UDF should not be used for such bulky data...
    Change "test" sub to the below and this is much faster than the one before.
    Please Login or Register  to view this content.
    You could also run this automatically each time you select "OVS Data" sheet via Worksheet_Activate event, if you can wait for a couple of seconds.
    Last edited by 6StringJazzer; 06-16-2019 at 09:07 PM. Reason: Attachment with sensitive data removed

  17. #17
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    This is significantly quicker :D

    Thank you very much!!

  18. #18
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by jindon View Post
    OK, UDF should not be used for such bulky data...
    Change "test" sub to the below and this is much faster than the one before.
    Please Login or Register  to view this content.
    You could also run this automatically each time you select "OVS Data" sheet via Worksheet_Activate event, if you can wait for a couple of seconds.

    Hi,

    Is it possible to make an addition to this code or add a new vba code for the following?

    On Sheet 1 - Column I2 onwards is where data is pasted to look up.
    On Sheet 2 - Column L2 onwards 2 is where the data is located
    On Sheet 1 - Column U2 onwards is where the result is output (If the data matches, leave the value blank. If the data doesn't exist return a value of Invalid)
    Last edited by exceleratevba; 08-05-2019 at 11:50 AM.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    I don't recall what this is all about, so if you upload a workbook with before/after, it will help.

  20. #20
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by jindon View Post
    I don't recall what this is all about, so if you upload a workbook with before/after, it will help.
    Of course, it was a while back.

    I've attached a sample of the previous spreadsheet.
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    That's what the current code is doing, I need to see your desired result.

  22. #22
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by jindon View Post
    That's what the current code is doing, I need to see your desired result.
    I had sample data added to both sheets, was there something specific you need to see?


    Column I on sheet 1 is where the sample data is pasted
    Column L on sheet 2 is where the lookup data is located (The data is always 10 digits long)
    Column U on sheet 1 is where I'd like the result to be output. (If the data is valid, leave the cell blank. If the data doesn't exist return a value of "Invalid")

    For instance if the value in I was "9999999999", the result should be "Invalid" on column U since it isn't on the list on the second sheet.
    If the value was "9999" or any other number of digits less than 10, the result would be "Invalid" as well.

    Does that help?
    Last edited by exceleratevba; 08-10-2019 at 08:00 AM.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    Add line in bold at the bottom of the code.
    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Quote Originally Posted by jindon View Post
    Add line in bold at the bottom of the code.
    Please Login or Register  to view this content.
    Awesome, thanks!

    I tried to make a couple of changes to formula since I've shifted my columns over (only the column positions have changed) but I'm getting an error when running it.

    The lookup value is in column F on the first sheet.

    On the first sheet; myCols = Array is now in columns DQ to DX.

    On the second sheet; my data is in columns I to S
    Attached Files Attached Files

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Lookup and return multiple values multiple cells

    1) Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    2) Change 9 to 6
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    11-07-2017
    Location
    CANADA
    MS-Off Ver
    2010
    Posts
    44

    Re: Lookup and return multiple values multiple cells

    Great, thank you!

+ 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] How to do multiple lookup and return multiple values
    By nawas in forum Excel General
    Replies: 5
    Last Post: 05-01-2018, 04:17 AM
  2. Replies: 2
    Last Post: 05-26-2015, 07:29 PM
  3. [SOLVED] Is it possible to lookup multiple values and return multiple rows of results?
    By justin11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2013, 12:02 PM
  4. lookup multiple values in multiple columns to return a result
    By AYAHOO123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2013, 07:53 PM
  5. Lookup multiple values in multiple columns and return value
    By Mads2600 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-20-2010, 09:57 AM
  6. Replies: 4
    Last Post: 07-07-2010, 12:36 AM
  7. Lookup in Multiple Columns, Return Multiple Values
    By andy62 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-05-2006, 09:40 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