+ Reply to Thread
Results 1 to 4 of 4

Vlookup multiple values to be shown

Hybrid View

  1. #1
    Registered User
    Join Date
    06-27-2019
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    2

    Question Vlookup multiple values to be shown

    Hi guys

    I need to create a project plan where we see on a calendar which projects are on which date. I managed to do this with Vlookup on my own, but the problem is that it only shows the first project on that date.

    I need it to show all projects that are on that date, not only the first one. Attached you find a very simple version of my project plan, maybe someone can help me? As you can see on 01.01.2019 it only shows Project 1, but it should show Project 1 and Project 3 as they are both on the same date.

    Would really appreciate some help on this!

    Best regards

    Zueri
    Attached Files Attached Files

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

    Re: Vlookup multiple values to be shown

    One of many answers https://fiveminutelessons.com/learn-...le-values-list

  3. #3
    Registered User
    Join Date
    06-27-2019
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    2

    Re: Vlookup multiple values to be shown

    Yeah I saw that but my excel skills are unfortunately not so good. I have not figured out how to use it with the table that I have

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup multiple values to be shown

    A left field approach:

    Use Power Query to transform your source data:

    EventList:
    let
        Source = Excel.CurrentWorkbook(){[Name="Events7"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Event", type text}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <> null and [Date] <> ""),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {{"Events", each Text.Combine(_[Event],"#(lf)"), type text}})
    in
        #"Grouped Rows"
    Close and load to Data Model.

    Add a Measure to the data model:
    All Events:=
    IF ( 
    	HASONEVALUE ( EventList[Date] ), 
    	FIRSTNONBLANK ( EventList[Events], 1 ), 
    	BLANK() 
    )
    Now in your "Calendar", you can use this formula (and format cells to wrap text):
    Formula: copy to clipboard
    =IFERROR(CUBEVALUE("ThisWorkbookDataModel","[EventList].[Date].&["&TEXT(F6,"yyyy-mm-dd")&"T00:00:00]","[Measures].[All Events]"),"")


    Use Data > Refresh All to update values after changes made to source data.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ 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. Replies: 1
    Last Post: 01-03-2018, 12:51 PM
  2. [SOLVED] Multiple MAX values to be shown
    By coach.32 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-24-2013, 04:48 PM
  3. [SOLVED] Named Ranges shown (or not shown) as blue means what?
    By wdeleo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  4. Named Ranges shown (or not shown) as blue means what?
    By wdeleo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. Named Ranges shown (or not shown) as blue means what?
    By wdeleo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. [SOLVED] Named Ranges shown (or not shown) as blue means what?
    By wdeleo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. Named Ranges shown (or not shown) as blue means what?
    By wdeleo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10: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