Forum Discussion
Select Spill Range Column
Hi,
Please is there a way to reference the second column of a spill range?
I want to run a search based on the range values.
Situations:
1) You have a spill range 10 x 1 in A2#. In column B you add an Xlookup (A2#.......) and magic. The formula spills nicely.
2) You have a spill range 10 x 2 in A2#. In column C you want to run an Xlookup with the values in column B as lookup values (i.e. the second column in A2#).
- If you use Xlookup(B2,....) you need to copy the formula down. Traditional way.
- You cannot pass B2# because the spill range is A2# and the formula doesn't understand it.
- And if you pass A2# you're looking up for the wrong values.
Thanks,
Best
Deleted
You may use for example =INDEX(A2#,,2) to return second column of the spill
4 Replies
Deleted
You can also use XLOOKUP to return a column from a dynamic array, assuming a range or array of column headers exists somewhere
= XLOOKUP( columnName, header, DArray# )
This is a range reference within the existing dynamic array and can be intersected with row ranges if required (as can the INDEX solution).
Because, as you see, I like naming every data object of interest, I might name the dynamic column formula 'selectedColumn' in which case the next lookup may be given by
= XLOOKUP( value, firstColumn, selectedColumn )
PeterBartholomew1 Thank you so much. I love this approach too.
Deleted
You may use for example =INDEX(A2#,,2) to return second column of the spill
