+ Reply to Thread
Results 1 to 10 of 10

Lookup return multiple values with TEXTJOIN

  1. 06-06-2023, 02:29 AM #1
    yarlachiru
    yarlachiru is offline
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Lookup return multiple values with TEXTJOIN

    I have delivery plan with Project numbers and day wise timeline, in the timeline only on certain dates a text or number is mentioned , which means a milestone is occurring on that particular date , rest of the days its blank which means work in progress, I have created a separate sheet named calendar to show what date falls under which weeknumber. What i would like to do is create a different sheet with Weeknumbers on Columns and project numbers on rows, with TEXTJOIN formula i want to look or all the texts/numbers in that particular week show in one cell.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-06-2023, 02:35 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,900

    Re: Lookup return multiple values with TEXTJOIN

    Why don't you just add a row with the week number to the delivery plan? It could be hidden. You are making it unnecessarily convoluted the way you have it set up now.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 06-06-2023, 02:39 AM #3
    yarlachiru
    yarlachiru is offline
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Re: Lookup return multiple values with TEXTJOIN

    Hello Ali, thanks for the suggestion, but i need to maintain two different sheets because the access for different sheets should be given different set of people
    Reply With QuoteRegister To Reply
  4. 06-06-2023, 02:49 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,900

    Re: Lookup return multiple values with TEXTJOIN

    That's not a compelling reason not to have the week calculation in the delivery plan!

    Create a row for the week number in the delivery plan and use this:

    =INDEX(Calendar!$D$2:$D$223,MATCH('Delivery Plan'!CCK4,Calendar!$A$2:$A$223,0))

    Then in your result sheet, use this:

    =IFERROR(LET(f,FILTER('Delivery Plan'!$CCK$5:$CKX$14,'Delivery Plan'!$A$5:$A$14='Result-Weekly Plan'!$A3),TEXTJOIN(",",,FILTER(f,(f<>0)*('Delivery Plan'!$CCK$2:$CKX$2='Result-Weekly Plan'!C$1)))),"")
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-06-2023, 02:55 AM #5
    yarlachiru
    yarlachiru is offline
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Re: Lookup return multiple values with TEXTJOIN

    Thank you Ali, this is really helpful,
    just one more thing , instead of adding the Week num row in delivery plan, can i just add this lookup also to the formula you have given?
    I really dont want to disturb the original delivery plan format
    Reply With QuoteRegister To Reply
  6. 06-06-2023, 02:56 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,900

    Re: Lookup return multiple values with TEXTJOIN

    Why overcomplicate this? Simply HIDE the row in the delivery plan!
    Reply With QuoteRegister To Reply
  7. 06-06-2023, 03:02 AM #7
    yarlachiru
    yarlachiru is offline
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Re: Lookup return multiple values with TEXTJOIN

    Yes that makes sense, i will go with your suggestion , thanks for the big help.
    Just for my knowledge if its not too much to ask, can you show me, how i can achieve the result in the way i was asking, it would be great help
    Reply With QuoteRegister To Reply
  8. 06-06-2023, 03:05 AM #8
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,900

    Re: Lookup return multiple values with TEXTJOIN

    If you really MUST do it the convoluted way, here's the formula:

    =IFERROR(LET(f,FILTER('Delivery Plan'!$CCK$4:$CKX$13,'Delivery Plan'!$A$4:$A$13='Result-Weekly Plan'!$A2),d,FILTER(Calendar!$A$2:$A$223,Calendar!$D$2:$D$223='Result-Weekly Plan'!C$1),TEXTJOIN(",",,FILTER(f,(f<>0)*('Delivery Plan'!$CCK$3:$CKX$3>=MIN(d))*('Delivery Plan'!$CCK$3:$CKX$3<=MAX(d))))),"")

    Just for my knowledge if its not too much to ask,
    It's not too much to ask, it's just pointless when there's a much easier and more elegant solution!!! Less is more ... Don't make life harder by introducting unnecessary levels of complexity!

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 06-06-2023, 03:15 AM #9
    yarlachiru
    yarlachiru is offline
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Re: Lookup return multiple values with TEXTJOIN

    Thank you very much Ali
    Reply With QuoteRegister To Reply
  10. 06-06-2023, 03:17 AM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,900

    Re: Lookup return multiple values with TEXTJOIN

    If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lookup formula to return multiple hits on multiple rows and values from multiple columns
    By Mattr13 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2021, 07:21 PM
  2. Lookup Multiple Values Across Columns and Return All Matching Values from a Single Column
    By h123h in forum Excel General
    Replies: 1
    Last Post: 05-12-2020, 08:42 AM
  3. [SOLVED] Match two criteria and return multiple records - INDEX & AGGREGATE, TEXTJOIN function
    By remyte in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2019, 01:53 PM
  4. Lookup multiple values in a single cell (separated by commas) and then return the values
    By JDMusic in forum Access Tables & Databases
    Replies: 3
    Last Post: 03-10-2017, 07:56 PM
  5. Index lookup with 2 lookup values and then return multiple results - array formula
    By D.Lovell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  6. Return multiple values below the lookup value
    By qontrol in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2014, 03:10 AM
  7. [SOLVED] Lookup and return multiple Values
    By Neil in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1