Search Contextures Sites

 

30 Excel Functions in 30 Days

 

Contextures
Excel news
by email

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.


 

 

 

 

 

Learn how to create Excel dashboards.

 

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

Learn how to create Excel dashboards.

 

 

 

Excel Index and Match Functions

Use the INDEX and MATCH functions to pull data from a list. These functions can be used together, to create a powerful and flexible formula.

INDEX and MATCH Introduction

  • The INDEX function can return an item from a specific position in a list.
  • The MATCH function can return the position of a value in a list.
  • The INDEX / MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table.

Video: INDEX Function

In this video, you'll see how to use the INDEX function to:

  • Find sales amount for selected month
  • Get reference to specified row, column, area
  • Create a dynamic range based on count
  • Sort column of text in alphabetical order

There are other INDEX examples below the video. For written instructions for this video, see the Contextures Blog article: 30 Excel Functions in 30 Days: 24 INDEX

INDEX Function -- Arguments

The INDEX function has three arguments:

Excel INDEX MATCH 01

  1. array: Where is the list? If you use an absolute reference ($A$2:$B$4), instead of a relative reference (A2:B4), it will be easier to copy to formula to other cells. Or, name the lookup table, and refer to it by name.
  2. row_num: Which row has the value you want returned? In this example, the item in the third row will be returned.
  3. [column_num]: Which column has the value you want returned? In this example, the item in the second column will be returned.
 
A
B
C
D
E
1
  
Small
Med
Large  
2
Sweater
10
12
15
 
3
Jacket
30
35
40
 
4
Pants
25
30
35
 
5
         
6 Size Item Price    
7 Med Pants
?
   

INDEX Function -- Example

The INDEX function can return an item from a specific position in a specific column in a list. For example, what is the 3rd item in the 2nd column in the list below?

 
A
B
1
Item
Price
2
Sweater
10
3
Jacket
30
4
Pants
25
5
=INDEX($A$2:$B$4,3,2)
  1. Select the cell in which you want the result
  2. Type an equal sign, the INDEX function name, and an opening parenthesis:
          =INDEX(
  3. Select the cells that contain the list -- cells A2:B4 in this example
  4. Press the F4 key on the keyboard, to change the reference to an absolute reference: $A$2:$B$4. Then, if you copy the formula to another cell, it will continue to refer to the list of items.
  5. Type a comma, to separate the arguments
  6. Type the number of the item you want to return -- 3 in this example
  7. Type a comma, to separate the arguments
  8. Type the number of the column you want to return -- 2 in this example
  9. Finish with a closing parenthesis. The completed formula is:
          =INDEX($A$2:$B$4,3,2)
  10. Press the Enter key to see the result. 

Interactive INDEX Example

In the embedded Excel file shown below, you can see the INDEX function example. On Sheet 1, the row and column numbers are typed in the formula -- hard-coded.

NOTE: The interactive file might not be viewable on all devices or browsers.

On Sheet 2, the row and column numbers are in worksheet cells. Change the row or column number to change the formula result.

MATCH Function -- Arguments

The MATCH function has three arguments:

To view the steps in a short video, click here

  1. lookup_value: What value do you want to find in the list? You can type the value, or refer to a cell that contains the value.
  2. lookup_array: Where is the list?
  3. [match_type]: Match_type can be -1, 0, or 1. It tells Excel how to match the lookup_value to values in the lookup_array.
     1 -- find the largest value less than or equal to lookup_value
             (the list must be in ascending order)
     0 -- find the first value exactly equal to lookup_value. Lookup_array
             (the list can be in any order)
    -1 -- find the smallest value greater than or equal to lookup_value.
             (the list must be in descending order)
    Note: If match_type is omitted, it is assumed to be 1.

MATCH Function -- Example

The MATCH function can find a value in a list, and return its position. For example, where is "Jacket" in the list below?

 
A
B
 
1
Item
 
 
2
Sweater
 
 
3
Jacket
 
 
4
Pants
 
 
5
Jacket
=MATCH(A5,ItemList,0)
  1. Select the cell in which you want the result
  2. Type an equal sign, the MATCH function name, and an opening parenthesis:
          =MATCH(
  3. Type the value to find, or click on the cell that contains the value -- cell A5 in this example
  4. Type a comma, to separate the arguments
  5. Select the range that contains the list -- a range named ItemList in this example
  6. Type a comma, to separate the arguments
  7. Type the number for the Match type you want to use -- 0 was used in this example, because an exact match is required.
  8. Finish with a closing parenthesis. The completed formula is:
          =MATCH(A5,ItemList,0)
  9. Press the Enter key to see the result.

INDEX / MATCH -- Example

One advantage of the INDEX / MATCH functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column.

In this INDEX / MATCH example, the MATCH function will find the position of "Jacket" in column B, and the INDEX function will return the code from the same position in column A.

 
A
B
1
Code
Item
2
SW001
Sweater
3
JK002
Jacket
4
PN001
Pants
5
 
 
6
Jacket
JK002
  1. Set up the worksheet as shown at right
  2. Enter the following formula in cell B6:
          =INDEX($A$2:$A$4,MATCH(A6,$B$2:$B$4,0))
  3. Press the Enter key to see the result.

The MATCH function, MATCH(A6,$B$2:$B$4,0), returns 2, which is the position of "Jacket" in the list.

Then, the INDEX function, INDEX($A$2:$A$4,2), returns "JK002", which is the second item in the range $A$2:$A$4

INDEX / MATCH -- Example 2

The MATCH function can be used to return values for both the row_num and column_num arguments in the INDEX function.

 
A
B
C
D
1
 
Small
Med
Large
2
Sweater
10
12
15
3
Jacket
30
35
40
4
Pants
25
30
35
5
 
 
 
 
6
Size Item Price  
7
Med Pants
?
 
  1. Set up the worksheet as shown above
  2. Enter the following formula in cell C7:       =INDEX($B$2:$D$4,MATCH(B7,$A$2:$A$4,0),MATCH(A7,$B$1:$D$1,0))
  3. Press the Enter key to see the result.

The first MATCH function, MATCH(B7,$A$2:$A$4,0), returns 3, which is the position of "Pants" in the Items list.

The second MATCH function, MATCH(A7,$B$1:$D$1,0), returns 2, which is the position of "Med" in the Size list.

Then, the INDEX function, INDEX($B$2:$D$4,3,2), returns "30", which is the third item in the second column in the range $B$2:$D$4

INDEX / MATCH -- Example 3

To make the previous example even more flexible, you can use the INDEX function within the MATCH function, to look for values in the first row or column of a named table. (This INDEX / MATCH example is from a newsgroup posting by Peo Sjoblom)

 
A
B
C
D
1
Small
Med
Large
2
Sweater
10
12
15
3
Jacket
30
35
40
4
Pants
25
30
35
5
 
 
 
 
6 Size Item Price  
7 Med Pants
?
 

Set up the worksheet as shown above

  1. The green cells are a range named Table). (Instructions on naming a range)
  2. Enter the following formula in cell C7:      
    =INDEX(Table,MATCH(B7,INDEX(Table,,1),0),MATCH(A7,INDEX(Table,1,),0))
  3. Press the Enter key to see the result.

The first MATCH function, MATCH(B7,INDEX(Table,,1),0), looks for "Pants" in the first column of the Table range (A1:A4), and returns 4.

The second MATCH function, MATCH(A7,INDEX(Table,1,),0), looks for "Med" in the first row of the Table range (A1:D1), and returns 3.

Then, the INDEX function, INDEX(Table,4,3), returns "30", which is in the fourth row in the third column in the range named Table  

INDEX / MATCH -- Example 4

Instead of matching information in column headings, you may need to match information that's stored in the columns, as shown in the table below.

 
A
B
C
D
1
Code
Item
Size
Price
2
SW001
Sweater
Small
10
3
JK001
Jacket
Small
30
4
PN001
Pants
Small
25
5
SW002
Sweater
Med
12
6
JK002
Jacket
Med
35
7
PN002
Pants
Med
30
8
SW003
Sweater
Large
14
9
JK003
Jacket
Large
40
10
PN003
Pants
Large
35
11        
12 Item Size Price Code
13 Jacket Med
?
?

In this INDEX / MATCH example, instead of columns headings of Small, Med, and Large, the size is stored in column C.

You need to find the price from column D, when Jacket is in column B, and Med is in column C.

  1. Set up the worksheet as shown at right
  2. Enter the following formula in cell C13:      
    =INDEX($D$2:$D$10,MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))
  3. This is an array formula, so hold Ctrl + Shift, and press the Enter key to see the result.

Curly brackets will be automatically added to the formula (don't type them yourself!), so the final result will look like this:

{=INDEX($D$2:$D$10,MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))}

Next, create a similar INDEX/MATCH formula in cell D13, to get the correct code from column A.

For details on how this formula works, you can read the explanation on the Contextures blog: Check Multiple Criteria with Excel INDEX and MATCH

Note: You can adjust the ranges to match the data on your worksheet, but in Excel 2003 and earlier versions you can't refer to an entire column.  

Video: Find Best Price With INDEX, MATCH, MIN

By combining the INDEX function with MATCH and MIN, you can show the name of the store that has the lowest price. MIN calculates the lowest price, and MATCH locates that price in the row. INDEX returns the store name for the selected column.

For written instructions for this Excel INDEX function tutorial, see the Contextures Blog article: Find Best Price with Excel INDEX and MATCH

Video: Find Distance Between Cities with INDEX / MATCH

From a lookup table with distances between cities, you can use the INDEX and MATCH functions to show the mileage between two selected cities.

Watch this video to see the steps, and you can download the video's sample file to see how it works.

Troubleshoot the MATCH formula

Your MATCH formula may return an #N/A, even though the value you're looking for appears to be in the lookup array.

Text vs. Number

A common cause for this error is that one of the values is a number, and the other is text. For example, the lookup array may contain '123 (text), and the value to look up is 123 (a number). Or, if you have downloaded data from a database, it may contain text codes with leading zeros, e.g. 00123, and your Excel file may contain numbers formatted with leading zeros.

If possible, convert the text to numbers, using one of the methods shown here:
      Convert Text to Numbers

If you can't convert the data, you can convert the lookup value within the MATCH formula:

Lookup values are Text, and the table contains Numbers

If the lookup array contains numbers, and the value to look up is text, use a formula similar to the following:

=MATCH(--A5,ItemList,0)

The double unary (--) converts text to a number, and will work correctly even if the lookup values are numbers.

Lookup values are Numbers, and the table contains Text

If the lookup array contains text, and the value to look up is numeric, use a formula similar to the following:

=MATCH(A5 & ""),ItemList,0)
OR
=MATCH(TEXT(A5,"00000"),ItemList,0)

The TEXT function converts a number to text, and will work correctly even if the lookup values are text. In the first example, the & operator creates a text string from an unformatted number. In the second example, a number formatted with leading zeros (e.g. 00123) would match a text "number" with leading zeros.

Spaces in one value, and not the other

Another potential cause for no matching value being found is a difference in spaces. One of the values may contain leading spaces (or trailing, or embedded spaces), and the other doesn't. To test the values, you can use the LEN function, to check the length of each value.
For example:   =LEN(A5)     will return the number of characters in cell A5. It should be equal to the number of characters in the matching cell in the lookup table.

If possible, remove the unnecessary spaces, and the MATCH formula should work correctly. If you can't remove the spaces, use the TRIM function in the MATCH, to remove leading, trailing or duplicate spaces. For example:
    =MATCH(TRIM(A5),ItemList,0)

HTML characters in one value, and not the other

If you copied data from a web page, it may contain non-breaking space (&nbsp) characters. David McRitchie has written a macro to remove them, along with other spaces characters -- http://www.mvps.org/dmcritchie/excel/join.htm#trimall     

Download the Sample File

Download the sample INDEX/MATCH workbook

Excel Function Tutorials

SUM Function  
VLOOKUP Function  
INDEX / MATCH Functions   
COUNT Function  
INDIRECT Function  

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: August 12, 2014 10:38 PM