How to use the output of a CONCAT() function as an array in Excel

Question

I would like to use the output of a CONCAT() function as the title for an array within XLOOKUP(). The XLOOKUP() formula I am using that works is:

=XLOOKUP(MetricDesired,'2023-12-13_000_Report.txt'!$C:$C,'2023-12-13_000_Report.txt'!D:D)

However, I would like the lookup and return arrays to come from multiple files which index the "000" in the file name up to "001" and so on. I have these file names in a separate column (column B) with the hope of using the text in that column as part of the arrays, but I tried using the following formula and it outputs #N/A:

=XLOOKUP(MetricDesired,CONCAT("'",$B3,"'!$C:$C"),CONCAT("'",$B3,"'!D:D"))

I tried CONCAT() as a way to automate the filenames in the data retrieval but I suspect the result of that function is not suitable for an array name.

Posted a comment just now for you - not sure where it went, but won't type it again. — Solar Mike
– Solar Mike, Commented Sep 19, 2024 at 20:24

rotabor · Accepted Answer · 2024-09-19 20:50:00Z

0

The solution for you:

=XLOOKUP(MetricDesired,INDIRECT(CONCAT("'",$B3,"'!$C:$C")),INDIRECT(CONCAT("'",$B3,"'!D:D")))

The INDIRECT function returns the reference specified by a text string.

If you like to return values from all sheets specified somewhere in the column B (e. g. B1:B10):

=MAP(
  B1:B10,
  LAMBDA(
    t,
    XLOOKUP(MetricDesired,INDIRECT(CONCAT("'",t,"'!$C:$C")),INDIRECT(CONCAT("'",t,"'!D:D")))
  )
)

edited Sep 19, 2024 at 20:50

answered Sep 19, 2024 at 20:23

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anon · Accepted Answer · 2024-09-19 20:50:33Z

0

CONCAT creates text, XLOOKUP requires a reference: a matrix or array that it will lookup

You can create references with INDIRECT("text") or related formulas, they don't return text as results, but references to cells or areas

answered Sep 19, 2024 at 20:50

Anon

1

Collectives™ on Stack Overflow

How to use the output of a CONCAT() function as an array in Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related