Dynamic X Loopup using Tables - How to tell xlookup which table to use?

Question

Currently the formula in cell B3 works by only looking up the value in B2 in the "Total Debt/Capital" table of cells D1:K6. How do I make the formula in B3 dynamic so it will change which table it looks at based in the value in A2 ? So if A2 says "Net Debt / EBITDA" then it will use the table in V1:AC6 to return the value. The correct value returned should be "B".

Current Formula:

=XLOOKUP(B2,XLOOKUP(B1,D2:D6,E2:K6),E1:K1,,1,-1)

Answer 1

Try using the following formula:

=LET(
     _LookUpTable, SWITCH(A2,"Total Debt / Capital", Capital,
                             "Total Debt / EBITDA", EBITDA,
                             "Net Debt / EBITDA", NEBITDA, ""),
     XLOOKUP(B2,XLOOKUP(B1,TAKE(_LookUpTable,,1),
     DROP(_LookUpTable,,1)),E1:K1,,1,-1))

---

---

• Firstly, you need to define the tables accordingly, so for this example I have named the first table as Capital --> Range from D1:K6 , EBITDA for M1:T6 and lastly, NEBITDA for V1:AC6
• Then apply the above formula using LET() function to define variables.
• Using IFS() or SWITCH() you can return the _LookupTable and use as a reference array for the XLOOKUP() functions.
• Note that, I have not used anything for the header, because the header is constant across three. but you can make it dynamic as well.

---

Or, making it more clearer, with each steps:

=LET(
     _LookUpTable, SWITCH(A2,"Total Debt / Capital", Capital,
                             "Total Debt / EBITDA", EBITDA,
                             "Net Debt / EBITDA", NEBITDA, ""),
     _LookUpArrayO, TAKE(_LookUpTable,-(ROWS(_LookUpTable)-1),1),
     _LookUpArrayT, DROP(_LookUpTable,1,1),
     _Header, DROP(TAKE(_LookUpTable,1),,1),
     XLOOKUP(B2,XLOOKUP(B1,_LookUpArrayO,_LookUpArrayT),_Header,,1,-1))

---

Note: It can be made more shorter by using Volatile functions like INDIRECT() but avoided using it since its volatile in nature, will slow down the working functionality of Excel as well as it will keep recalculating whenever there is a change in any open workbook.

---