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+

Related Posts

Excel Subtotal Function – Include Hidden Val... What does it do? It returns a Subtotal in a list or database Formula breakdown: =SUBTOTAL(function_num, ref1) What it means: =SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data) ***Go to the bottom...
Return the Last Value in a Column with the Offset ... What does it do? It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells Formula breakdown: =OFFSET(reference, rows, columns, , ) What it means: =OFFSET(start in this cell, go up/down a number of ro...
Sumproduct & Weighted Averages   What does it do? It returns the sum of the products of corresponding ranges or arrays Formula breakdown: =SUMPRODUCT(array1, , ...) What it means: =SUMPRODUCT(this array, with that array…) A quick way to calculate the weighted average of two lis...
Concatenate With A Line Break   What does it do? Joins two or more text strings into one string. The item can be a text value, number, or cell reference. Formula breakdown: =CONCATENATE(text1, , , ...) What it means: =CONCATENATE(the first text, the second text, and so on...) ...