We are going to present one way to write
dynamic XLOOKUP formulas
based on different worksheet names.
As you can see there are several separate
worksheets by individual business units,
which presents budgeted Revenue,
EBITDA and operating cashflow.
Let's go to our worksheet summary, where we
want to present budgeted monthly numbers by
Revenue, EBITDA and Operating Cashflow by
each business unit for comparative purposes.
First we want to return the January
2021 revenue number for Alpha.
The syntax for the lookup_array and
return_array in this XLOOKUP formula
must be completely accurate.
Otherwise the formula won't return the
correct value, or any value at all.
As you can see, we apply the INDIRECT
function with double quotation, single quotation
and double quotation marks to dynamically
reference the name of the worksheet
Alpha in cell "B5".
The same process is required
for the return_array syntax too.
If the formula is correct, you should return
a value of 94 like I have here.
We can now copy the formula across.
Ensuring the syntax continues to reference cell
"B4" for "Revenue by Business",
as well as the worksheet name
in cell "B5" for "Alpha".
The only change you will need to make is
with the column range referenced in the
return_array syntax, which corresponds to
the changing month range for the budget.
Once you have updated the column range in
the return_array syntax, we can copy this range
down the page to the other business units.
To ensure our formulas are correct, we can
check out a few cells across different
worksheet to validate our XLOOKUP
and INDIRECT formulas are accurate.
We can now repeat this exercise with
the EBITDA by Business Unit.
Again remember the need to use double
quotation, single quotation and double
quotation marks to dynamically reference the
name of the worksheet "Alpha" in cell "B15".
Like we raised earlier, take your time with
writing out these formulas,
as they need to be 100% accurate.
If the formula is correct, the January 2021
EBITDA number for "Alpha" shall be 11.28.
Just as we did with the Revenue formulas in
the above, you need to update the formula
range lookup syntax across the time series to
return the correct monthly EBITDA number.
Let's validate some of these EBITDA
formulas across the worksheets.
We can fill these formulas down the page
for each business unit's EBITDA numbers.
Finally we can use the XLOOKUP
and INDIRECT formula to lookup the
individual operating cashflow numbers
by month for each business unit.
Like the exercise for Revenue and EBITDA, the
column range lookup syntax must be
adjusted to reference the correct month.
Once this is complete,
we can copy these formulas down the
page to the other business units.
We will do a spot check on some of these
Operating Cashflow numbers across the
source worksheets to ensure the
dynamic XLOOKUP is working correctly.
Feel free to send us a comment, like
this video or subscribe to our channel.