For convenience and readability,project(B5:B16) anddata(C5:F16) arenamed ranges.
It can also be solved with the FILTER function.
With XLOOKUP
With theXLOOKUP function, the solution is simple.
In thedynamic array version of Excel, the 4 values in C11:F11spillinto the range I5:L5.
Seethis pagefor more details and examples.
In thedynamic array version of Excel, these results willspillinto the range I5:L5.
To do this, simplynestthe formula above inside the other function.
If the project in H5 is changed, the formula recalculates, and a new result is returned.
The INDEX and MATCH and FILTER versions of the formula can be nested in the same way.
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…
