Xlookup with dynamic return array

Hi all, I am trying to create a dynamic xlookup function where the return array can change based on a user imputed dropdown menu. I have everything so far in a sample workbook, but my xlookup function returns a #VALUE! error. The formula currently looks like =XLOOKUP('Data Output'!E7&'Data Output'!!A9,TABLE[#ALL],MATCH(Inbetween!F5,TABLE[#Haders],0),"",0)

I have also attached a sample workbook that includes the scenario I am trying to fix. Many thanks in advance

How about

Formula:

Please Login or Register  to view this content.

This worked perfectly thank you! As a follow-up question if I wanted to use the formula to generate the cell location Ex cell E5 to use in a dynamic SUM formula how would I do that.

I have this formula but excel is saying the formula is not correct.

Please Login or Register  to view this content.

Also, would it work to have a SUM formula with two CELL formulas as the reference eg:

Please Login or Register  to view this content.

Thank you for your help!

Try

Formula:

Please Login or Register  to view this content.

That formula wasn't working for me but I adjusted it by adding a helper column through a macro to the table and a match function. It seems to work now Thank you for your help

New Formula:

Formula:

Please Login or Register  to view this content.

Glad you sorted it & thanks for the feedback.