Summary
This video looks at how to dynamically select columns.
Tab in example file: Dynamic column selection
The additional functions introduced in this video are TOROW and XMATCH.
TOROW / TOCOL – Returns an array or range as a single column (TOCOL) or single row (TOROW).
The syntax of TOROW / TOCOL is:
=TOCOL(array, [ignore], [scan_by_column])=TOROW(array, [ignore], [scan_by_column])- array – Array or range to return as a column/row.
- [ignore] – Defines values to ignore.
- [scan_by_column] is not used in this example.
XMATCH – Searches a range or array for a value and returns the position of the matching value.
The syntax of XMATCH is:
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])- lookup_value – The value to search for.
- lookup_array – The array or range to search in.
- [match_mode] and [search_mode] are not used in this example.
By combining TOROW, XMATCH and CHOOSECOLS we can return the columns we want in any order.