The Index and Match Functions Revisited

We have previously covered the INDEX and MATCH functions; in this tip we revisit these functions but with a different example. The INDEX and MATCH functions can be used as an alternative to the VLOOKUP function and has the added advantage of looking up values to the left of the lookup column/value. With the VLOOKUP function the values to be looked up must be to the right of the lookup value/column.

MATCH: Returns the relative position of an item in an array that matches a specified value in a specified order.

INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

In this tip we show how, by using an example of an invoice number as a lookup value; the relevant date, customer name and inclusive price can be extracted. The main list is placed on the data list worksheet and the lookup data (invoice numbers) on the lookup worksheet.

Note: Download the sample workbook to practice this exercise.

Applies to: Microsoft Excel 2007, 2010 and 2013

The screen shots below will be used for this example.

Data List worksheet

tip-1

Lookup worksheet

tip-2

 

 

 

 

 

1.     To extract the date value:

a)     Select cell B2 on the lookup worksheet.

b)    Copy and paste this formula:
=INDEX(‘Data List’!A2:H26,MATCH(A2,’Data List’!H2:H26,0),1).

c)     Format the value to a date value.

d)    Copy the formula down as shown below.

tip-3

 

 

 

 

 

Note:

  • ‘Data List’!A2:H26 – refers to the array or range of cells for the source data
  • MATCH(A2,’Data List’!H2:H26,0)

Occupies the position of the row number in the INDEX function.
A2 is the invoice number (IN000017) on the lookup worksheet.
H2:H26 is the data range for the invoice numbers on the data list worksheet.
0 finds the first value that is exactly equal to the lookup value.
1 is the position of the Date column on the data list worksheet.

2.     To extract the customer name:

a)     Select cell C2 on the lookup worksheet.

b)    Copy and paste this formula:
=INDEX(‘Data List’!A2:H26,MATCH(A2,’Data List’!H2:H26,0),2).

c)     Copy the formula down as shown below.

tip-3

 

 

 

 

 

3.     To extract the total price:

a)     Select cell D2 on the lookup worksheet.

b)    Copy and paste this formula
=INDEX(‘Data List’!A2:H26,MATCH(A2,’Data List’!H2:H26,0),7).

c)     Copy the formula down as shown below.

tip-5