 SOLVED

# Making a variable naming array in formula

Occasional Contributor

# Making a variable naming array in formula

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

3 Replies
best response confirmed by pawswag (Occasional Contributor)
Solution

# Re: Making a variable naming array in formula

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.

# Re: Making a variable naming array in formula

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)))

# Re: Making a variable naming array in formula

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