For convenience and readability,quarter(C4:F4) anddata(C5:F16) arenamed ranges.
With XLOOKUP
With theXLOOKUP function, the solution is straightforward.
In thedynamic array version of Excel,the 12 values in E5:E16spillinto the range H5:H16.
With FILTER
You might not think of using theFILTER functionto filter columns, but it works fine.
For another example of using FILTER on horizontal data,see this page.
In thedynamic array version of Excel, these results willspillinto the range H5:H16.
InLegacy Excel, you will need to enter this formula as amulti-cell array formula.
This is simply a matter ofnestingthe lookup formula into another function.
The same approach can be used with the INDEX and MATCH and FILTER versions of the formula.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you’ve got the option to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
Often, MATCH is combined with the…
