SOLVED

Look Up and Dynamic Array

%3CLINGO-SUB%20id%3D%22lingo-sub-3219324%22%20slang%3D%22en-US%22%3ELook%20Up%20and%20Dynamic%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219324%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20again%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CP%3EI'm%20not%20an%20advanced%20user%20but%20am%20familiar%20with%20bunch%20of%20excel%20formulas.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20want%20to%20dynamically%20retrieve%20all%20the%20values%20from%20a%20table%20field.%20After%20doing%20some%20research%20I%20have%20come%20to%20think%20that%20it's%20possible%20with%20array%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20wasn't%20able%20to%20get%20it%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20there%20is%20a%20table%20with%20employee%20names%20and%20I%20want%20to%20get%20all%20of%20their%20names%20based%20on%20a%20shift%20hour%20which%20are%20also%20listed%20in%20the%20table%20in%20a%20index%20match%20or%20xlookup%20friendly%20format.%20The%20table%20will%20be%20dynamic%20so%20I%20want%20my%20formula%20to%20be%20dynamic%20as%20well%20that's%20why%20I%20thought%20I'd%20need%20an%20array%20formula.%3C%2FP%3E%3CP%3EThis%20is%20my%20formula%20but%20I'm%20getting%20an%20error.%20I'm%20thinking%20I'm%20doing%20a%20logical%20error%20with%20my%20cube%20formulas.%3C%2FP%3E%3CPRE%3E%7B%3DCUBEVALUE(%22ThisWorkbookDataModel%22%2CCUBEMEMBER(%22ThisWorkbookDataModel%22%2C%22%5BRegulated%5D.%5BShift%5D.%5B%22%26amp%3B%24M%242%26amp%3B%22%5D%22)%2CCUBEMEMBER(%22ThisWorkbookDataModel%22%2C%22%5BRegulated%5D.%5BName%5D%22))%7D%3C%2FPRE%3E%3CP%3EI've%20also%20tried%20index%20and%20match%20and%20xlookup%20but%20failed%20again.%3C%2FP%3E%3CPRE%3E%7B%3DINDEX(Regulated%5BName%5D%2CMATCH(%24M%242%2CRegulated%5BShift%5D%2C0))%7D%3C%2FPRE%3E%3CPRE%3E%7B%3DXLOOKUP(%24M%242%2CRegulated%5BShift%5D%2CRegulated%5BName%5D)%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20want%20to%20note%20that%20there%20are%20blank%20cells%20in%20between%20employee%20names%20which%20is%20unavoidable%20due%20to%20the%20structure%20of%20the%20table%20requirements.%3C%2FP%3E%3CPRE%3E%7B%3DRegulated%5BName%5D%7D%3C%2FPRE%3E%3CP%3EThis%20simple%20formula%20retrieves%20all%20the%20employees%20from%20all%20the%20shifts.%20However%2C%20what%20I%20need%20it%20to%20do%20is%20exclude%20blank%20cells%20and%20be%20dynamic%20depending%20on%20the%20shift%20hour%20I%20select%20from%20a%20drop%20down%20list%20on%20M2.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20attaching%20a%20sample%20workbook%20to%20demonstrate%20what%20I%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20express%20that%20the%20table%20is%20neatly%20formatted%20because%20it%20helps%20me%20visualize%20things%20better.%20Queue%20Assignment%20Mid%20Sheet%20is%20how%20the%20table%20formatted%20in%20default.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20really%20appreciate%20your%20assistance.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3219324%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3219358%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20and%20Dynamic%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142876%22%20target%3D%22_blank%22%3E%40kheldar%3C%2FA%3E%26nbsp%3B%20Well%20maybe%20here%20is%20another%20function%20for%20you%20to%20learn%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%20FILTER().%26nbsp%3B%20It%20is%20new%20with%20the%20introduction%20of%20dynamic%20arrays.%26nbsp%3B%20There%20is%20also%20some%20other%20great%20new%20functions%20including%20UNIQUE%2C%20SEQUENCE%2C%20and%20LET%20to%20just%20name%20a%20couple.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(Regulated%5BName%5D%2CRegulated%5BShift%5D%3DTEXT(%24M%242%2C%22hh%3Amm%22)%2C%22none%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Bnote%20I%20had%20to%20add%20the%20TEXT()%20to%20your%20M2%20because%20the%20shift%20time%20is%20converted%20to%20text.%26nbsp%3B%20I%20would%20recommend%20getting%20rid%20of%20that%20and%20just%20format%20that%20column%20to%20display%20in%20that%20format%20instead%2C%20but%20that%20is%20just%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3232409%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20and%20Dynamic%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3232409%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20bother%20you%20with%20something%20related%20with%20filter%20function%3F%20After%20researching%20a%20bit%20I've%20learnt%20that%20filter%20doesn't%20natively%20work%20with%20dynamic%20cell%20references%20%3CSTRONG%3E%23.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DXLOOKUP(A2%23%26amp%3B%22Meal%20Break%22%2CRawMMP%5BName%5D%26amp%3BRawMMP%5BBreak%5D%2CRawMMP%5BMinutes%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20the%20alternative%20I'm%20using.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20does%20not%20work%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(RawMMP%5BDuration1%5D%2CRawMMP%5BName%5D%3DA2%23%2C%22na%22)%2C%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3235308%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20and%20Dynamic%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3235308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142876%22%20target%3D%22_blank%22%3E%40kheldar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20both%20%3CSTRONG%3ERawMMP%5BName%5D%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EA2%23%3C%2FSTRONG%3E%20are%20arrays.%26nbsp%3B%20The%20first%20problem%20is%20that%20the%20result%20of%20such%20a%20comparison%20is%20ambiguous.%26nbsp%3B%20If%20we%20assume%20that%20each%20element%20of%20the%20spilt%20array%26nbsp%3B%3CSTRONG%3EA2%23%3C%2FSTRONG%3E%20is%20to%20be%20compared%20at%20a%20time%2C%20the%20result%20would%20be%20an%20array%20of%20overlapping%20filtered%20ranges%2C%20so%20still%20no%20use.%3C%2FP%3E%3CP%3EYou%20either%20need%20to%20write%20a%20separate%20formula%20for%20each%20element%20of%20%3CSTRONG%3EA2%23%26nbsp%3B%3C%2FSTRONG%3Eand%20space%20them%20out%2C%20or%20you%20turn%20to%20more%20advance%20programming%20techniques.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20MAP(distinctName%23%2C%0A%20%20%20%20%20%20LAMBDA(name%2C%0A%20%20%20%20%20%20%20%20%20%20TEXTJOIN(%22%2C%20%22%2C%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20FILTER(RawMMP%5BDuration1%5D%2CRawMMP%5BName%5D%3Dname%2C%22na%22)%0A%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20takes%20each%20element%20of%20A2%23%20(I%20have%20assumed%20it%20to%20be%20a%20list%20of%20distinct%20names)%20and%20produces%20a%20filtered%20array.%26nbsp%3B%20I%20used%20TEXTJOIN%20to%20turn%20the%20filter%20array%20result%20into%20a%20comma-separated%20list.%26nbsp%3B%20MAP%20brings%20the%20lists%20together%20as%20an%20array%20of%20lists.%3C%2FP%3E%3CP%3ETo%20put%20the%20result%20together%20as%20a%20composite%20list%20required%20advanced%20techniques%20which%20I%20have%20already%20discussed%20today%20within%20another%20post.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hello again,

I'm not an advanced user but am familiar with bunch of excel formulas.

I want to dynamically retrieve all the values from a table field. After doing some research I have come to think that it's possible with array formulas.

 

However, I wasn't able to get it done.

 

Basically there is a table with employee names and I want to get all of their names based on a shift hour which are also listed in the table in a index match or xlookup friendly format. The table will be dynamic so I want my formula to be dynamic as well that's why I thought I'd need an array formula.

This is my formula but I'm getting an error. I'm thinking I'm doing a logical error with my cube formulas.

{=CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Regulated].[Shift].["&$M$2&"]"),CUBEMEMBER("ThisWorkbookDataModel","[Regulated].[Name]"))}

I've also tried index and match and xlookup but failed again.

{=INDEX(Regulated[Name],MATCH($M$2,Regulated[Shift],0))}
{=XLOOKUP($M$2,Regulated[Shift],Regulated[Name])}

 

I also want to note that there are blank cells in between employee names which is unavoidable due to the structure of the table requirements.

{=Regulated[Name]}

This simple formula retrieves all the employees from all the shifts. However, what I need it to do is exclude blank cells and be dynamic depending on the shift hour I select from a drop down list on M2.

I'm attaching a sample workbook to demonstrate what I need.

 

I want to express that the table is neatly formatted because it helps me visualize things better. Queue Assignment Mid Sheet is how the table formatted in default.

 

I'd really appreciate your assistance.

Thanks.

7 Replies
best response confirmed by kheldar (Contributor)
Solution

@kheldar  Well maybe here is another function for you to learn   FILTER().  It is new with the introduction of dynamic arrays.  There is also some other great new functions including UNIQUE, SEQUENCE, and LET to just name a couple.

=FILTER(Regulated[Name],Regulated[Shift]=TEXT($M$2,"hh:mm"),"none")

 note I had to add the TEXT() to your M2 because the shift time is converted to text.  I would recommend getting rid of that and just format that column to display in that format instead, but that is just me.

Wow! That worked like a charm! Thanks a lot!

@mtarler 

 

Can I bother you with something related with filter function? After researching a bit I've learnt that filter doesn't natively work with dynamic cell references #.

 

 

 

=XLOOKUP(A2#&"Meal Break",RawMMP[Name]&RawMMP[Break],RawMMP[Minutes])

 

This the alternative I'm using.

 

However,

 

This does not work:

 

=FILTER(RawMMP[Duration1],RawMMP[Name]=A2#,"na"),

 


Is it possible to make filter behave as such?

 

 

 

 

@kheldar 

The problem is that both RawMMP[Name] and A2# are arrays.  The first problem is that the result of such a comparison is ambiguous.  If we assume that each element of the spilt array A2# is to be compared at a time, the result would be an array of overlapping filtered ranges, so still no use.

You either need to write a separate formula for each element of A2# and space them out, or you turn to more advance programming techniques.

= MAP(distinctName#,
      LAMBDA(name,
          TEXTJOIN(", ",,
              FILTER(RawMMP[Duration1],RawMMP[Name]=name,"na")
          )
      )
  )

This takes each element of A2# (I have assumed it to be a list of distinct names) and produces a filtered array.  I used TEXTJOIN to turn the filter array result into a comma-separated list.  MAP brings the lists together as an array of lists.

To put the result together as a composite list required advanced techniques which I have already discussed today within another post. 

 

Thank you for your response. Is Lambda available for use outside of Insider channel now?
So your example of won't work is correct, it won't work because you are doing a comparison of a column of names to an array of values (A2#). I'm trying to understand the relationship between the Filter example and the Xlookup example and not sure how the values translate but a couple of points.
-When possible (i.e. when you want to return a single value/array) try to use XLOOKUP as it will probably be faster/less burden on Excel.
- Based on the xlookup example it looks like you want multiple conditions so to do that in Filter use * to AND conditions or + to OR conditions:
-FILTER( [table/array], ([column a]="value1") * ([column b]="value2") + ([column c]="value3") , "na" )
This will return all the rows from [table/array] where the corresponding values in a & b = value1 & value2 respectively OR the corresponding value in column c is value3
I see Peter already replied. As for Lambda, yes it is in the current channel and it appears most have it but technically may still be 'rolling out'.