Using INDIRECT within an XLOOKUP to be able to use a drop down list for sheet selection

[Lawless83]

Afternoon All,

I am trying to write a formula to be able to dynamically change the sheet the xlookup is looking through using a drop down menu. I have been using a similar thread on this forum to try to get there but Im failing at the last hurdle. Excel is telling me there is a typo and then adding asterisks when I ask it to correct.

Here is the current formula I have:

=XLOOKUP(A3,INDIRECT("'"&$B$1&"!")*A3:A532,INDIRECT("'"&$B$1&"!")*AC3:AC532)

The drop down menu cell is B3. When using this formula Im getting a N/A even though there is data in that selection to be shown.

I'm sure more information will be needed. Forgive me, new to the forum and relatively new to using excel in a more advanced way. Please let me know what information is needed to help you help me

 

[ETAF]

try
=XLOOKUP(A3,INDIRECT("'"&$B$1&"'!A3:A532"),INDIRECT("'"&$B$1&"'!AC3:AC532"))

its usefult o know what office you are using - as different functions are in later versions of excel

you may be able to use trim range to ignore empty cells - and also choose or switch to save using the volitile indirect() function

 

[Lawless83]

try
=XLOOKUP(A3,INDIRECT("'"&$B$1&"'!A3:A532"),INDIRECT("'"&$B$1&"'!AC3:AC532"))

You are truly a legend, worked perfectly.

If its not too much trouble could you explain where I was going wrong? Enjoying learning this stuff tbh

 

[ETAF]

not sure if using an * for a range works
this bit
Dont know as i have never seen or used

INDIRECT("'"&$B$1&"!")*A3:A532

so changed to be part of the indirect()
INDIRECT("'"&$B$1&"'!"A3:A532")

also the finishing ' was missing
for the sheet name if there are spaces in the sheet name

'sheet name'

only issue will be if you copy the formula - the range does NOT change at all

another more experienced member my know how to use the * with an indirect

 

[Lawless83]

not sure if using an * for a range works
this bit
Dont know as i have never seen or used

INDIRECT("'"&$B$1&"!")*A3:A532

so changed to be part of the indirect()
INDIRECT("'"&$B$1&"'!"A3:A532")

also the finishing ' was missing
for the sheet name if there are spaces in the sheet name

'sheet name'

only issue will be if you copy the formula - the range does NOT change at all

another more experienced member my know how to use the * with an indirect

I appreciate your explanation thankyou.

Excel was the one putting in the * after telling me I had made a mistake.

I see what you mean about including the range in the indirect. Makes sense.

I really appreciate your time thankyou