SOLVED

Making a variable naming array in formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3073478%22%20slang%3D%22en-US%22%3EMaking%20a%20variable%20naming%20array%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073478%22%20slang%3D%22en-US%22%3EHi%20All%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20currently%20using%20a%20XLOOKUP%20formula%20to%20return%20data%20in%20a%20named%20range%20array%20(with%20both%20x%20and%20y%20variables).%20Here%20is%20the%20formula%20that%20works%20for%20me%20for%20case1%3B%3CBR%20%2F%3E%3DXLOOKUP(%24D%2412%2Ci.case1.category%2C%20XLOOKUP(%24S%2411%2Ci.case1.years%2Ci.case1.data))%3CBR%20%2F%3E%3CBR%20%2F%3ENow%20I%20would%20like%20to%20be%20able%20to%20select%20a%20case%20number%20from%20a%20drop%20down%20list%20and%20then%20the%20formula%20will%20update%20and%20reference%20the%20different%20case%23%20within%20the%20formula.%20Is%20this%20possible%3F%20I%20have%20started%20including%20IF%20statements%20but%20ultimately%20want%20to%20put%20a%20variable%20in%20for%20a%20naming%20array%20eg%20i.case1.data%20vs%20i.case2.data%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073478%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3073547%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20a%20variable%20naming%20array%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073547%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1288341%22%20target%3D%22_blank%22%3E%40pawswag%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20can%20solve%20that%20with%20the%20INDIRECT%20function.%3C%2FP%3E%3CP%3EI%20have%20put%20a%20very%20simplified%20example%20because%20I%20do%20not%20know%20how%20your%20table%20looks%20like.%20But%20it%20should%20give%20you%20at%20least%20an%20idea%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DTE_1-1643268895778.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342881i40DD73EA4C2F0A31%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22DTE_1-1643268895778.png%22%20alt%3D%22DTE_1-1643268895778.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DXLOOKUP(%24D%2412%3BINDIRECT(%24D%2413%26amp%3B%22category%22)%3BINDIRECT(%24D%2413%26amp%3B%22data%22))%3C%2FP%3E%3CP%3ESo%2C%20depending%20on%20the%20case%20you%20enter%20in%20D13%2C%20it%20picks%20the%20values%20from%20the%20first%20or%20from%20the%20second%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
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!
3 Replies
best response confirmed by pawswag (Occasional Contributor)
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:

DTE_1-1643268895778.png

=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.

 

 

 

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

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