None of these cells are merged.
Two-way INDEX and MATCH
Essentially, this formula uses a standardtwo-way INDEX and MATCH formula.
The INDEX function is provided with the data to return, and the MATCH function is used two times.
However,the regular MATCH function will work fine as well.
The result is that we retrieve the value in thenext column, which holds the value for Length.
By providing the values inrows, we get back avertical array.
As in the original example above, XMATCH matches on size and returns a column number.
CHOOSECOLS then returns the specified column.
Let me know if you disagree!
But OFFSET is avolatile functionso I avoid using it when possible.
XLOOKUP on the other hand deals withranges.
To make column ranges dynamic, you sometimes need to use another function like CHOOSECOLS.
you’ve got the option to use INDEX to retrieve individual values, or entire rows and columns.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
The columns to return are provided as numbers in separate arguments.
Each number corresponds to the numeric index of a column in the given array.
