Simpler Column Lookups

For Tiller production templates and also for Builders :wrench: 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):

  1. Lookup the column header name with a formula like: =SUBSTITUTE(ADDRESS(1,MATCH("Description",INDIRECT("'Transactions'!$1:$1"),0),4),1,"")
  2. Create a range from the column letter with a concatenation formula like: ="Transactions!"&A2&"2:"&A2
  3. 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ā€¦ :thinking:
ā€¦ and this seems to work:
=XLOOKUP("Description",Transactions!1:1,Transactions!A2:M)

One Hiccup :face_with_diagonal_mouth:

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)))

Questions :ear:

  • Are builders already doing this?
  • Are there any limitations that Iā€™m not seeing?
  • Do you have further improvements?
3 Likes

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!

2 Likes

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!

1 Like

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:

=XLOOKUP("Description",Transactions!1:1,Transactions!2:100)

And if you wanted to include them all, then you could do:

=XLOOKUP("Description",Transactions!1:1,INDIRECT("Transactions!2:"&ROWS(Transactions!A:A)-1))

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:

=XLOOKUP("Description",Transactions!1:1,Transactions!2:200000000)

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.

1 Like

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.

Some things that come to mind for me, @randy :thinking:

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 XLOOKUP OFFSET formula is lengthy, as well ā€¦ just thinking if it gets substituted for INDIRECT(B2) type references.

2 Likes

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:

  1. The names of the columns Iā€™m looking for: (in A1:D1) Date,Description,Amount,Category
  2. MATCH to get the column number: (in A2) =ARRAYFORMULA(MATCH(A1:D1, Transactions!$1:$1, 0))
  3. CHOOSECOLS below that to fill in the data: (in A3) CHOOSECOLS(Transactions!$1:$1, A2:D2))
1 Like