For Tiller production templates and also for Builders looking to map cored-data (e.g. transactions or balances) from Tiller-filled sheets to their template dashboards, we long ago settled on a clumsy brute force convention (often in the hidden area):
Lookup the column header name with a formula like: =SUBSTITUTE(ADDRESS(1,MATCH("Description",INDIRECT("'Transactions'!$1:$1"),0),4),1,"")
Create a range from the column letter with a concatenation formula like: ="Transactions!"&A2&"2:"&A2
Use an INDIRECT formula to use the generated range string wherever the column data is needed with a formula like: =INDIRECT(B2)
This morning, I got to wondering if there was an easier wayā¦
ā¦ and this seems to work: =XLOOKUP("Description",Transactions!1:1,Transactions!A2:M)
One Hiccup
The only bummer Iām aware of is that there doesnāt seem to be a great way to use the whole sheet range without hardcoding an end row or end column (which will vary of course sheet to sheet).
So maybe we need to replace the last argument with: OFFSET(Transactions!$A$2, 0, 0, Rows(Transactions!$A:$A)-1, Columns(Transactions!$1:$1))
The final formula would look like this (and could render the range in line): =XLOOKUP("Description",Transactions!1:1,OFFSET(Transactions!$A$2, 0, 0, Rows(Transactions!$A:$A)-1, Columns(Transactions!$1:$1)))
Nice! I hadnāt tried that method, but will keep it in mind for any future work. I copy/pasted it into my template, and it did return my āDescriptionsā. Thanks!
Love it! Thanks Randy. XLookup is awesome. Thanks too for your builder help with the team this morning. I had such fun rolling up my sleeves and going into builder mode!
Hi @Randy - I like it! I havenāt used that method so far but Iām gonna consider it further. For some smaller builds, Iāve been using a LET function to eliminate the need for the hidden table by just storing the column letter as a defined variable in the formula itself. Iāll let you know if I think of any other limitations or improvements, but one possible simplification that initially came to mind is that I donāt believe you have to reference columns in the third argument. So if you know how many rows you want to include, for example the first 100, you could do:
And actually Iām not aware of a negative consequence to just entering an end row number that is sufficiently larger than you would ever see. For example:
Thanks for the correctionā and testā @jpfieber. I corrected the formula in the original post and deleted the correction-note from your post so others donāt erroneously delete a paren from the updated formula.
Thatās great to hear, @peter. Would be fun to try to bundle up that little builder-learning-project we did as a team this morning for a share in the community.
1. Manual Transaction (or simply inserting a row above row 2) will break the intended formula
as: Transactions!$A$2
will change to: Transactions!$A$3
etc. for every time that happens.
Using an INDIRECT string reference fixes that.
2. Can the extra row/column calculations be avoided by overshooting the column range? =XLOOKUP("Description",Transactions!1:1,INDIRECT("Transactions!A2:ZZZ"))
Noting that if I used ZZZZ (four Zās, instead of three), I got this error message: #N/A Error Array arguments to XLOOKUP are of different size.
So, maybe bumping into some limit.
My Transactions sheet goes up to column Z.
3. If #2 is not viable, is calculating the number of rows in a larger dataset for every copy of this formula a possible performance issue. Perhaps it isnāt noticed when interactively testing, but does every bit help? The SUBSTITUTE(ADDRESS( method certainly operates on much smaller data input that rarely changes.
4. The XLOOKUPOFFSET formula is lengthy, as well ā¦ just thinking if it gets substituted for INDIRECT(B2) type references.
Iāve used a 3 step staging process that works for certain access patterns. If I want data from a number of columns in one sheet, I have the following staging rows:
The names of the columns Iām looking for: (in A1:D1) Date,Description,Amount,Category
MATCH to get the column number: (in A2) =ARRAYFORMULA(MATCH(A1:D1, Transactions!$1:$1, 0))
CHOOSECOLS below that to fill in the data: (in A3) CHOOSECOLS(Transactions!$1:$1, A2:D2))