Excel tips: Vlookup vs Index/Match

You can tell we’re starting to get a bit adventurous with Excel, when we start to use the VLOOKUP formula. But could an Index/Match combo be better?

VLookup

The Vlookup formula will look for something in one area of a spreadsheet, and return the result from another area (sometimes even from different sheets or files altogether):

Foe example, if we had a list of Product Codes and their Selling Prices, we could enter the Product Code elsewhere, and the Vlookup would return the Selling Price from the list (as per the yellow cell below), without us having to go and find it manually.

The Vlookup formula used here would be as follows:

That’s ACE… right?

Limitations of a VLookup

Well, yes it is, but there are a couple of restraints with this formula:

• The Vlookup will only find the ‘Row Number’, you have to manually define the column to use This is defined by the 3 in the formula: =VLOOKUP(B3,’Selling Prices’!$A$3:$C$12,3,FALSE) and represents the third column in the range).

• The Vlookup will only return a result to the right of the column where it finds a match

• The Vlookup can only look for an ‘Exact Match’ or a ‘Nearest Match’

Free Excel webinar

Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below

View webinar

Index / Match Formula

There is another formula we could use that negates all of these issues, and that is a combination of ‘Index / Match’.

The ‘Index’ formula will look at a range and return a result from the row and column that you specify i.e.:

Unfortunately, most of the time we won’t know which Row & Column we want to use… and that’s where the ‘Match’ formula comes in, as we can use that to define the Row & Columns.

This is the formula that has been used in cell C3, in this example:

=INDEX(‘Selling Prices’!$A$2:$C$12,MATCH(B3,’Selling Prices’!$A$2:$A$12,0),MATCH(A3,’Selling Prices’!$A$2:$C$2,1))

This will return the correct Price from the table, for the selected Date (A3) and Product (B3).

This looks like a big scary formula, and would put most people off using it, so here is a more detailed explanation:

• INDEX(‘Selling Prices’!$A$2:$C$12,

This is the ‘Index’ part of the formula, and is basically saying: Look in the Range A2:C12 in the sheet ‘Selling Prices’.

• MATCH(B3,’Selling Prices’!$A$2:$A$12,0),

This is the 1st ‘Match’, and will return the Row Number to use.

This formula is saying Match the value from B3, within the range of A2:A12 (in the sheet: Selling Prices), and find an exact match (this is what the 0 at the end represents).

• MATCH(A3,’Selling Prices’!$A$2:$C$2,1))

This is the 2nd ‘Match’, and will return the Column Number to use.

This formula is saying Match the value from A3, within the range of A2:C2, and find a value less than A3 (this is what the 1 at the end represents).

The formula would return the result for the Matches first, which would make the formula look like this: =INDEX($A$2:$C$12,5,3)

This is basically saying: from the range A2:C12, what is the value in Row 5, Column 3.

In summary

The Index/Match formula can return a result from any column, not just columns to the right of the match. It can also return both Row & Column (unlike Vlookup). The Match formula also gives 3 options: Less Than (1), Exact Match (0) & Greater Than (-1), so gives greater flexibility than the Vlookup.

The Index/Match is a little trickier to use than a Vlookup, but it is real GENIUS, and it’s really worth trying to understand it a little better. 

Browse the full range of AAT study support resources here

Traci Williams is a self confessed ‘Spreadsheet Geek’ and has spent more than half of her life working with Excel Spreadsheets. She launched her own Excel training business, www.excelace.co.uk, and has since trained over 500 Delegates, and worked with over 200 businesses.