Excel advanced formulas

The famous INDEX / MATCH is a combination of two functions. If you followed my VLOOKUP guide you would see some similarities.

I urge you to do this hands on with me. All the guides online combined won't do anything unless you try it along. Practice is important!

First - lemme briefly explain what an index is.

An index refers to the position within an ordered list.

In the picture above, the index number is 4 for E.

An index starts at 0 and counts up 1 for each new element in the list.

In Excel, however, the Index starts at 1.

You can think of the table you are using as a grid.

With one index for up and down, and left and right.

Kind of like coordinates in battle ship!

So to break it down, using the table below we have:

Row Index [Value B]


Column Index [Value C]


Time for some GIF action.

First we break down each function to show you the pieces.

The second GIF will combine 'em all into one big function to help "optimize" your spreadsheet.

And there ya have it. You can now leverage Excel formulas to pull data from ANYWHERE in a table based off of the headers. This is extremely flexible and can be used for many applications.

If you have a specific application you think this could work for but can't quite to seem to reach the solution, please don't hesitate to ask!

E-mail me at thomas.p.sullivan14@gmail.com.