What does it do?

It returns a cell´s value from within a table/range

Formula breakdown:

=INDEX(array, row_num, [column_num])

What it means:

=INDEX(from this table/range, return me this row number, [and return me this column number])


The INDEX function in Excel returns a cell´s values from within a table/array.

It works like a map, so you have to select a range (table/array) and tell it to return you the coordinates (Row & Column numbers).

So if you want to return values from a Price List or large data set, then your INDEX function is your savior.

DOWNLOAD EXCEL WORKBOOK

We want to get the price of a laptop in 2014 and 2015 based on price table.

STEP 1: We need to enter the INDEX function in a blank cell:

=INDEX(

Index formula

 

STEP 2: The INDEX arguments for the 2014 laptop price:

Array

What is the table of source data?

We need to select the pricing table here.

=INDEX(C16:E19,

Index formula

Row_num

What row number contains the data?

Since we want the laptop, it’s on row #2

=INDEX(C16:E19, 2,

Index formula

Column_num

What column number contains the data?

Since we want the price for the year 2014, it’s on column #2

=INDEX(C16:E19, 2, 2)

Index formula

 

STEP 3: The INDEX arguments for the 2015 laptop price:

Array

What is the table of source data?

We need to select the pricing table here.

=INDEX(C16:E19,

Index formula

Row_num

What row number contains the data?

Since we want the laptop, it’s on row #2

=INDEX(C16:E19, 2,

Index formula

Column_num

What column number contains the data?

Since we want the price for the year 2015, it’s on column #3

=INDEX(C16:E19, 2, 3)

Index formula

You now have your prices!

Index formula

How to Use the Index Formula in Excel

Index_Intro

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+