Microsoft Excel Index Match Lookup

• Microsoft Excel Vlookup

In the last lesson we learned to use Vlookup. Vlookup is very efficient for what it does, but it can become time consuming when thousands of formulas are needed. The case below will illustrate a situation with there is a more efficient alternative.

Let assume this scenario:

You work in the retail banking industry. Your manager has asked you to pull details for a random list of 400 transactions. You are able to export a master list of all transaction details stored on your banks system into an excel file. You must now build a formula to pull the details you need from the table.

I have simplified the transactions needed to 5 for illustration purposes.

If you were to Vlookup instead of the Index Match for the above scenario above it would take roughly three times longer for excel to calculate the formula results. You may think this isn't a big deal but as you begin working with large sets of data it could likely be the difference between 10 and 3 minutes in excels calculation time.

When using Vlookup excel searches a range for a match. In this case for all of the formulas on the same row excel is duplicating the same search. This is very inefficient.

By using the Index function in conjunction with the Match function you can eliminate the duplicate calculations and greatly improve the efficiency with which the formulas are calculated.

The index function is extremely fast when compared to Vlookup. It allows you to reference a cell in a table by defining its column and row number.

=index(Array,RowNumber, ColumnNumber)

Array: This is the range of your table

RowNumber: This is the row number within the table for cell value you want to return

ColumnNumber: This is the column number within the table for cell value you want to return

Let's Look at an Example

The data above represent bushel sales of apples, oranges, and pears. We want to return the number of pears on January 2. Define the following three parameter in your formula to return the data desired.

• Define our table range B3:D12.
• Define row number within table 2
• Define column number within table 3

The match function will return the row or column number for a matched criteria in a given range.

=match(lookup_value,lookup_array,match_type)

Lookup Value: This is value you want to match.

Lookup Array: This is the single row or column range you want to return a match in.

Match Type: Use "true" to return an exact match.

Let's consider the same example above. We want to find the row number for 1/2/2012.

We would like to return the number 2 signifying a match is in the second row of our range. We must define the following:

• Lookup Value: The value we want to match. In this case we must use the date function to convert the date to a value excel understands. I will cover working with dates in Excel at a later date.
• Lookup Array: The single row or column in which we want to search for a match. In this case A3:A12.
• Match Type: Use false to return an exact match

Now that we know how to return the row number of match within our table, we can use this formula within Vlookup to return the value we want to find. Simple replace the row number with the match formula from above.

=INDEX(B3:D12,MATCH(DATE(2012,1,2),A3:A12,FALSE),3)

You should now be able to see how we can perform a task similar to Vlookup by using Index and Match functions.

Now that we know how to return the row number we need we can complete this task one time and reference it in subsequent formulas.

Begin by inserting a column to right of our lookup data. This column will be use to search for a match in our lookup range

=MATCH(G3,A3:A4596,FALSE)

Note: 4,596 is the last row of my data in my table.

Now reference the row number H3 in our three formulas:

• =INDEX($B$3:$E$4596,$H3,1)
• =INDEX($B$3:$E$4596,$H3,3)
• =INDEX($B$3:$E$4596,$H3,4)

Copy all of the formulas down to the end of your lookup criteria.

You now know how to create to lookup values from a table as a speed that will likely far exceed your peers.