Example solutions for reverse lookup from data held within a crosstab table.

[Peter Bartholomew]

Which attracts you more (or repels you least)? Both PowerQuery and Dynamic Arrays have the potential to radically change Excel solutions.
Do you see such solutions as simpler or more complex than traditional Excel solutions? Which would you recommend (if either).
The attached workbook demonstrates both approaches. The PQ solution is simply unpivot and filter but automating the refresh and reading a parameter add to the overall complexity of the solution. The dynamic array solution concatenates row and column headings and filters those before distributing terms from the combined text string to a spilt range.

p.s. I am not sure which sub-forum is the most appropriate for such discussion so if any moderator wishes to reassign it, just go ahead.

 

[AlanSidman]

Peter, as you would probably guess, I am more attracted to the PQ solution. My expertise with arrays is sufficiently lacking and therefore more comfortable with PQ.

 

[GraH - Guido]

Same as Alan. Being a PQ user for about 5-6 years, I can say it is in my comfort zone of practice. Not so with array formulas, with or without the dynamic array functions. Those are not even available at work! Without that practical application I cannot anticipate how that would affect my ways of dealing with Excel challenges.
I do experiment with DA-functions at home and I know I will use them. The potential is obvious, no doubt. Are they in competition with PQ? Not in my line of work. I mostly use PQ to combine data from different sources of to perform heavy data cleansing. DA potentially replace loads of my pivots and helper columns. Almost all my professional workbooks are "binary" solutions. I use 1/0 in tons of columns for whatever metric I would be needing.

 

[Peter Bartholomew]

I think

in my line of work

may be the key. I have memories of a statement that over 95% of spreadsheet use is for analysis of data extracted from databases (presumably that is once one has discounted shopping lists and the like).

That came as a surprise to me; I had almost never needed to analyse data from a database. Pivot tables were largely an irrelevance to me (with the exception of a single contract that specified about 500 pivot charts - I really struggled to control the series formats when the legend categories changed from chart to chart).

Nearly everything I did was building mathematical models; that includes: spectral analysis of the motion of a warship in the North Atlantic; the presentation of statistical results derived from surveys; calculating the path of light through a lens system, presenting results from the recent FIFA World Cups including repositioning national flags to reflect the calculation of group qualification criteria; generating engineering sketches that are dimensioned to illustrate specific datasets; and demonstrating options for financial motels.



I am presumably not alone; the survey result above would suggest that spreadsheets are used somewhere to support the creation of the majority of engineered products that you might buy.

Despite that, I really like Power Query for importing and cleaning data lists. I took encouragement from the success of PQ that the traditional 'hell in a cell', bottom-up view of the universe and its associated cryptic notation can be avoided. For years I have used Named formulae to do much of the same thing with ordered data rather than lists. Named formulae allowed me to work at array level without considering individual elements of the array (no implicit intersection).

Now with dynamic arrays, the results can be presented fluently, rather than needed to resort to CSE and creating models that felt as if they were cast in concrete. No more 'tips and tricks'; and far less reliance on 'keyboard shortcuts.