Scribd
Upload
15 views1 page

Financial Analysis

This guide provides instructions for performing dynamic financial analysis in Excel using advanced formulas and functions. It explains how to use the INDEX and MATCH functions to retrieve specific values from a table, such as net income and revenue for a given year. By combining these functions, users can create dynamic formulas that allow for easy data lookup based on user-defined inputs.

Uploaded by

Malik Waqar Hussain CA CPA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
15 views1 page

Financial Analysis

This guide provides instructions for performing dynamic financial analysis in Excel using advanced formulas and functions. It explains how to use the INDEX and MATCH functions to retrieve specific values from a table, such as net income and revenue for a given year. By combining these functions, users can create dynamic formulas that allow for easy data lookup based on user-defined inputs.

Uploaded by

Malik Waqar Hussain CA CPA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Dynamic Financial Analysis

This guide will teach you how to perform dynamic financial analysis in Excel
using advanced formulas and functions.

INDEX, MATCH, and INDEX MATCH MATCH Functions


1. The INDEX function works similarly to the VLOOKUP function by returning
a value in a table based on the intersection of a row and column position
within that table. For example, say we want to find the net income for 2018
from the Income Statement section. In cell E49, we use the INDEX
formula =INDEX(A15:I23,9,6) to look up the 9th row and 6th column in the
income statement table to extract the net income value.

2. The MATCH function searches for a specified item in a range of cells and then
returns the relative position of the item in the range. For example, suppose we want
to find out the row and column where the 2018 net income is located in the Income
Statement table. We can use the MATCH formula =MATCH(D49,A15:A23,0) to
find the row position and =MATCH(E47,A5:R5,0) to find the column position.

3. Combining the INDEX and MATCH functions, we can create a dynamic


formula to search for the value we want from the Income Statement table.
For example, assume we want to look up the revenue amount in 2018. In
cell E48,
enter =INDEX($A$15:I23,MATCH($D48,$A$15:$A$23,0),MATCH(E$47,
$A$5:$R$5,0)). You can quickly look up a number in a specific year by
changing the blue colored input.

You might also like