Making a variable naming array in formula

[pawswag]

Hi All,

I currently using a XLOOKUP formula to return data in a named range array (with both x and y variables). Here is the formula that works for me for case1;
=XLOOKUP($D$12,i.case1.category, XLOOKUP($S$11,i.case1.years,i.case1.data))

Now I would like to be able to select a case number from a drop down list and then the formula will update and reference the different case# within the formula. Is this possible? I have started including IF statements but ultimately want to put a variable in for a naming array eg i.case1.data vs i.case2.data. These datasets (different cases) are in different workbooks.

Using windows10 and excel for 365 version 2102

Thanks in advance!

[#2 · Accepted solution]

Hi @pawswag 

I think you can solve that with the INDIRECT function.

I have put a very simplified example because I do not know how your table looks like. But it should give you at least an idea:

=XLOOKUP($D$12;INDIRECT($D$13&"category");INDIRECT($D$13&"data"))

So, depending on the case you enter in D13, it picks the values from the first or from the second table.

[#3]

Thank you @Martin_Weiss , I have been trying INDIRECT formulas but not having much luck.

Managed to get the desired result using IFS but it is very long winded - 

=IFS($E$1=$E$4,XLOOKUP($D12,i.case1.category,XLOOKUP(S$11,i.case1.years,i.case1.data)),$E$1=$E$5,XLOOKUP($D12,i.case2.category, XLOOKUP(S$11, i.case2.years, i.case2.data)),$E$1=$E$6,XLOOKUP($D12,i.case3.category,XLOOKUP(S$11,i.case3.years,i.case3.data)), $E$1=$E$7,XLOOKUP($D12,i.case4.category,XLOOKUP(S$11,i.case4.years,i.case4.data)), $E$1=$E$8,XLOOKUP($D12,i.case5.category,XLOOKUP(S$11,i.case5.years,i.case5.data)), $E$1=$E$9,XLOOKUP($D12,i.case6.category,XLOOKUP(S$11,i.case6.years,i.case6.data)))

[#4]

Think I cracked it with;

=XLOOKUP($D$12, INDIRECT($E$2&”.category”), XLOOKUP (S$11,INDIRECT($E$2&”.years”),INDIRECT($E$2&”.data”)))

Much cleaner! Thank you