Indirect used in xlookup

[itsrich]

Whenever I sit down to use INDIRECT, it messes with my brain! Once I get it correct, it is wonderful! It is the combination of "'"&A2&'"' that messes me up.

This is my current formula =XLOOKUP(A2,'[2025 Whole Schedule Bulk Items.xlsm]Cass'!$D:$D,'[2025 Whole Schedule Bulk Items.xlsm]Cass'!$F:$F,"Obsolete?")

I know I can replace the red and green text with INDIRCT. (it will be used to replace the same text in the Return_array part of the formula)

The file name in red will be in Cell B2

The tab name in green will be in Cell C2

This formula will be dragged down the Column (I will change the cell references to fit my needs, the syntax of the INDIRECT part of the formula kicks my backside)

Your brains will save me time. TYIA

 

[dreid1011]

How about this:

INDIRECT("'"&B2&C2&"'!$D:$D")

 

[Fluff]

How about

=XLOOKUP(A2,INDIRECT("'"&B2&C2&"'!$D:$D"),INDIRECT("'"&B2&C2&"'!$F:$F"),"Obsolete?")

 

[itsrich]

Fluff, YOU ARE AMAZING!!
dreid1011 yours took me a little more thinking!

​

Adding this to my Thug Notes spreadsheet! This is great stuff! TY so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[dreid1011]

Fluff, YOU ARE AMAZING!!
dreid1011 yours took me a little more thinking!

​

Adding this to my Thug Notes spreadsheet! This is great stuff! TY so much!

Curious as I provided the exact same thing as Fluff did for the first INDIRECT, I just didn't expand it to the rest of the formula. My apologies. And you're welcome.

 

[itsrich]

You can see by my numbers, that I have been a member of Mr Excel for a few years. I can't thank you all enough. You have made my work much easier! TY!

dreid1011, you provided EXACTLY what I asked for. TYVM!

I should follow up and explain what I had to do to make these formulas work.

I started with this =XLOOKUP(A2,'[2025 Whole Schedule Bulk Items.xlsm]Cass'!$D:$D,'[2025 Whole Schedule Bulk Items.xlsm]Cass'!$F:$F,"Obsolete?")

I am using this =XLOOKUP(A2,INDIRECT("'"&B2&C2&"'!$D:$D"),INDIRECT("'"&B2&C2&"'!$F:$F"),"Obsolete?")

B2 = [2025 Whole Schedule Bulk Items.xlsm]
C2 = Cass

I hope this fills in the blanks for others who may come looking.

I will figure out how to move the [ and .xlsm] from B2 into the INDIRCT later. I got to here INDIRECT("'["&L1&".xlsm]'"&C1&"'!$D:$D"), but get a #REF. Again TYVM!

 

[Fluff]

You need to remove the ' after the ]

 

[itsrich]

You need to remove the ' after the ]

TY Fluff!

XLOOKUP(B3,INDIRECT("'["&$L$1&".xlsm]"&L3&"'!$D:$D"),INDIRECT("'["&$L$1&".xlsm]"&L3&"'!$F:$F"),"Obsolete?")

$L$1 = File name
L3 = Tab name

It is those pesky "'" combinations.

• I see the single quote is where it would be normally in the formula.
• The dbl quotes go around text
• The & goes around the cell reference.

Is there a better way to describe the use of these characters with in INDIRECT?

 

[Fluff]

No, that's about right.