LiveFlow's Budget vs Actuals report dynamically organizes financial data, making it essential to correctly use INDEX/MATCH/MATCH to extract the right values. This guide will help you apply the function accurately.
Understanding the Structure of LiveFlow’s Budget vs Actuals Report
LiveFlow's Budget vs Actuals report has a unique column header format:
The first row (merged across three columns) displays the month and year (e.g., Jan 2025).
The second row contains:
Budget (1st column)
Actual (2nd column)
Variance (3rd column)
Additionally, LiveFlow automatically generates six named ranges that update dynamically when the report is refreshed, ensuring that formulas always reference the latest data. To learn more about LiveFlow named ranges check out Guide to Named Ranges in LiveFlow.
Why Does This Matter?
When using INDEX/MATCH/MATCH, we must account for the two-row column headers to extract Budget, Actual, or Variance values correctly.
A standard INDEX/MATCH/MATCH formula referencing the ColumnLabels named range directly will not work when extracting "Actual" or "Variance" data because the ColumnLabels named range contains two rows. To fix this, we must adjust our approach by incorporating an additional INDEX function.
Correcting the INDEX/MATCH/MATCH Formula for Budget vs Actuals Reports
Basic INDEX/MATCH/MATCH Syntax (Won't Work for "Actual" or "Variance")
=INDEX({SheetName}_Contents,
MATCH(lookup_row_value, {SheetName}_RowLabels, 0),
MATCH(lookup_column_value, {SheetName}_ColumnLabels, 0))
⚠ Issue: Since BudgetvsActuals_ColumnLabels includes both header rows, this formula may return an incorrect column reference when searching for "Actual" or "Variance".
The Correct Approach: Using Nested INDEX
To correctly reference Actual or Variance, we must adjust the formula by using an INDEX within the second MATCH function to extract only the first row of column labels.
Updated Formula Format:
=INDEX({SheetName}_Contents,
MATCH(lookup_row_value, {SheetName}_RowLabels, 0),
MATCH(lookup_column_value, INDEX({SheetName}_ColumnLabels, 1), 0))
What’s New?
INDEX({SheetName}_ColumnLabels, 1): Extracts the first row of column labels (month and year).
MATCH(lookup_column_value, INDEX({SheetName}_ColumnLabels, 1), 0): Ensures the formula correctly aligns with the month and year header.
How to Extract Budget, Actual, or Variance?
Since "Budget" is always the first column under each date, we need to adjust the formula to shift the column reference accordingly:
Values to Extract | Formula Adjustment |
Budget (1st column) | No adjustment needed |
Actual (2nd column) | Add +1 to the column match to move to the next column |
Variance (3rd column) | Add +2 to the column match column match to move to the next column |
Final Formula with Adjustments
=INDEX({SheetName}_Contents,
MATCH(lookup_row_value, {SheetName}_RowLabels, 0),
MATCH(lookup_column_value, INDEX({SheetName}_ColumnLabels, 1), 0) +1 or +2)
✅ Budget:
=INDEX({SheetName}_Contents,
MATCH(lookup_row_value, {SheetName}_RowLabels, 0),
MATCH(lookup_column_value, INDEX({SheetName}_ColumnLabels, 1), 0))
✅ Actual:
=INDEX({SheetName}_Contents,
MATCH(lookup_row_value, {SheetName}_RowLabels, 0),
MATCH(lookup_column_value, INDEX({SheetName}_ColumnLabels, 1), 0) +1)
✅ Variance:
=INDEX({SheetName}_Contents,
MATCH(lookup_row_value, {SheetName}_RowLabels, 0),
MATCH(lookup_column_value, INDEX({SheetName}_ColumnLabels, 1), 0) +2)
Key Takeaways
✔ Always use INDEX({SheetName}_ColumnLabels, 1) to extract month names for proper column matching.
✔ Adjust the formula for Actual and Variance by adding +1 or +2 in the column MATCH function.
✔ Leverage LiveFlow's dynamic named ranges to keep formulas up-to-date automatically.
This method ensures accurate data extraction for Budget, Actual, and Variance values in LiveFlow’s Budget vs Actual report. 🚀