Usefulness and example of using Index and Match functions on excel - Unlocked
Originally published: 11/06/2020 15:04
Publication number: ELQ-69404-1
View all versions & Certificate
certified

Usefulness and example of using Index and Match functions on excel - Unlocked

Simple example for using Index-Match function and comparison to Offset function.

Description
This tool is for the intermediate and advanced excel user.
This is simple unlocked excel table with 2 key example tables of how to use INDEX MATCH to search and find to pinpoint and call out specific cells in complex tables. Second table explains the difference using the same example with OFFSET function.
Both of the examples use 3 variables to point to the data. First variable "Population" points to which of the 4 tables to focus on. Then followed by the "YEAR" variable and "MONTH" variable. These serve as x and y axes to call out the cell data that is being targeted. The the output cell provides the data that comes out.
In this first example, you may change the 3 variables in the 3 cells that are in light blue.
Moving to the next tab that is marked "Offset" the output is calculated using the OFFSET function. This again uses the 3 parameters of which chart then year and month. In addition it uses the MATCH function to help locate at which cell each of these variables are and provide the 2 inputs to the OFFSET function.
As an intermediate and advanced excel user you will understand the difference, pros and cons of each method.

This Best Practice includes
1 Excel document

Aram K offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss


2.0 / 5 (1 votes)

please wait...