Alternative to VLOOKUP

Without a doubt for anyone between an administrator to a data analyst the VLOOKUP function in Excel is a great tool.
Grabbing data from one dataset and putting it onto another based solely on one criterion can be a powerful asset.
However there is a better and quicker way to process your data. It's a combination of INDEX and MATCH, two operations seldom used by the "Normal" user.
Let’s strip the codes.
=VLOOKUP(Criteria, Array, Column number, Exact or closest match)
=INDEX(Array, Row Number, Column Number)
=MATCH(Criteria, Array, Type)
Pretty similar I agree, so let’s put them into practice.
Set the scene, used cells Columns A-Y and rows 1-1000 giving 25000 used cells, we can eradicate row 1 for our headings so more like 24974 used cells, we are going to keep it simple and imagine the data is sorted in order using Column A (Excel calculates sorted data a little quicker).
For the purpose of this the datasheet is on Sheet2.

In cell B1 the formula =VLOOKUP(A1,Sheet2!A1:Z1000,20,0)
Lookup the Value of A1 in the data on Sheet2 and return the value of the 20th consecutive column with an exact match. Hey presto it works perfectly.
But what would happen if you needed to add a column somewhere between 1st and 20th?
Correct you would have to edit your formula to include the column even without using absolute references, meaning your range and your column number would have to be amended. Imagine 5 Columns have this formula in across the 1000 rows, bit of a pain.

In cell B1 the formula =MATCH(A1,Sheet2!A:A,0)
Match the Value of A1 in Column A in the data on Sheet2 with an exact amount. This just gives us the row number that the value is first in.

INDEX will only return the value of a specified cell, we'd have to specify the column number and the row number to get the value we're looking for, hmmm.....
So if...
In cell C1 the formula =INDEX(Sheet2!T:T,B1,0)
The resulting formula goes = value of the cell that is in the 20th Column (T:T) on nth row (B1). Hey presto it works perfectly.
But what would happen if you needed to add a column somewhere between 1st and 20th?
Nothing at all, all of the references are dynamic. If you add a column at B the above INDEX formula becomes =INDEX(Sheet2!U:U,B1,0).
The INDEX, MATCH formula can be combined into one cell but only do this if you really need to.
=INDEX(Sheet2!T:T,MATCH(A1,Sheet2!A:A,0),0)

The reason is in the calculation timers
Using the data range above (with live data)
VLOOKUP took 4.54 seconds
INDEX and MATCH took 3.31 seconds (already a winner)
However splitting the two took only 0.41 seconds.
Combining INDEX MATCH is still beneficial over VLOOKUP but the true power is shown when the two calculations are split (especially over a larger data range). This is a data sheet of only 24974 cells

Think of the time saved comparing a full data range of excels monstrous 17,179,869,184 cells (quick exaggerated calculation)
VLOOKUP: 36.15 days
INDEX MATCH (Combined): 26.36 days
INDEX MATCH (Split): 3.25 days

So get using it data lovers.

Our Blog Bites

The Courthouse Bar Ards

Our latest website creation is within the hospitality trade.
The Courthouse Restaurant and Bar in Newtownards came to PW Design after multiple…

Alternative to VLOOKUP

Without a doubt for anyone between an administrator to a data analyst the VLOOKUP function in Excel is a great tool.
Grabbing…

Being a small company

Small businesses, they say, have a personality, flavour and sensibility that big businesses have lost. And when it comes to what you put…

Build your own website?

BYO? Good or Bad.
What is the deciding difference between a Build your own website and employing a professional organisation to deploy…

Do you really need a website

In 2014, 38 million adults (76%) in Great Britain accessed the Internet every day almost twice as much compared to 2007.
73%…

Need a website created now?

If you need a site up and running as soon as possible or you just don't have the time to dedicate working with…

Happy New Year, Happy New Prices

2013 is now here and we've decided to review our pricing packages with reductions across all of our design packages and hosting.

EU Cookie Regulations

Britain now has to conform to the EU Regulations regarding the use of Cookies on an Internet site.(albeit a watered down version).