Meet fellow New Skills Academy students in our study group. Join now!

Microsoft Excel Advanced Certification

100%
Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Duration: Approx 50 minutes

31.1 INDEX MATCH Function


The INDEX MATCH Function enables the use of INDEX and MATCH to get information from a table, based on an exact match.

By default, a standard lookup with INDEX and MATCH isn't case sensitive; it just returns the first match and ignores factors such as case. If you need to do a case-sensitive lookup, you can do so by using an array formula that uses INDEX, MATCH, and the EXACT function to bring about the desired results.

The formula is written as: =INDEX(array, MATCH(lookup_value,lookup_array,[match_type]))

31.2 Video Instruction



INDEX MATCH Function in Excel – 1m 26s






31.3 Task


Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded, follow the video above and you will see how to use the INDEX MATCH function.

Download Worksheet

Option #2: Worksheet without formula

Download Worksheet


31.4 INDEX MATCH Function 2


The INDEX MATCH function enables the use of INDEX and MATCH to get data from two separate areas.


One in a column and one in a row.


The formula is written as: =INDEX(array,MATCH(lookup_value,array,0)

31.5 Video Instruction



INDEX MATCH Function 2 in Excel – 1m 12s






31.6 Task


Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded, follow the video above and you will see another way to use the INDEX MATCH function.

Download Worksheet

Option #2: Worksheet without formula

Download Worksheet


31.7 INDEX MATCH Function 3


The INDEX MATCH function enables the use of INDEX and MATCH to get data from two separate areas.


This video shows another way of using the function in this way.


The formula is written as: =INDEX(array,MATCH(lookup_value,array,0)


31.8 Video Instruction



INDEX MATCH Function 3 in Excel – 1m 24s






31.9 Task


Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded, follow the video above and you will see another way to use the INDEX MATCH function.

Download Worksheet

Option #2: Worksheet without formula

Download Worksheet


31.10 INDEX MATCH Function 4


Instead of using VLOOKUP that can only look up values on the far left, INDEX and MATCH can help you look up a value in any column and return the value to the left.


The MATCH function returns the position of a value in a specific range. Use this result and the INDEX function to return a value.


The formula is written as: =INDEX(array,MATCH(lookup_value,array,0)


31.11 Video Instruction



INDEX MATCH Function 4 in Excel – 1m 22s






31.12 Task


Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded, follow the video above and you will see another way to use the INDEX MATCH function.

Download Worksheet

Option #2: Worksheet without formula

Download Worksheet


31.13 INDEX MATCH Function 5


Looking up a value in a table using both rows and columns is known as a multiple lookup.


You can write a formula that performs a multiple lookup by using INDEX and MATCH. The core of this formula is the INDEX, which refers to retrieving a value from the data based on a row number and a column number. To get those row and column numbers, use MATCH, configured for an exact match, by setting the argument to 0.


The formula is written as: =INDEX(array,MATCH(lookup_value,array,0),MATCH(lookup_value,array,0))


31.14 Video Instruction



INDEX MATCH Function 5 in Excel – 1m 46s






31.15 Task


Estimated Time: 10 minutes
Download the worksheet that accompanies this video by clicking below. Once downloaded, follow the video above and you will see another way to use the INDEX MATCH function.

Download Worksheet

Option #2: Worksheet without formula

Download Wworksheet


<< Previous Next >>

Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


Learning Topics


1
Module 1: Add a Password
20 minutes
2
Module 2 : AND Function
20 minutes
3
Module 3 : Auto Updating Drop-down Lists
20 minutes
4
Module 4 : CEILING Formula
20 minutes
5
Module 5 : Charts that Update Automatically
20 minutes
6
Module 6 : CHOOSE Formula
20 minutes
7
Module 7 : Compare 2 Lists with VLOOKUP Function
20 minutes
8
Module 8 : CORREL Function
20 minutes
9
Module 9 : Create an Advanced Calculator
20 minutes
10
Module 10 : Create a Combo Chart
20 minutes
11
Module 11 : Create a Custom Tab
20 minutes
12
Module 12 : Create a Pivot Chart
20 minutes
13
Module 13 : Create Visual In-cell Indicators
20 minutes
14
Module 14 : Create Visual In-cell Indicators with Icon Sets
20 minutes
15
Module 15: Customize the Status Bar
20 minutes
16
Module 16: DATEDIF Function
20 minutes
17
Module 17: DATEVALUE Function
20 minutes
18
Module 18: DAVERAGE Function
20 minutes
19
Module 19 : DCOUNT Function
20 minutes
20
Module 20 : DCOUNTA Function
20 minutes
21
Module 21 : Depreciation Formula
20 minutes
22
Module 22 : DGET Function
20 minutes
23
Module 23 : DMAX Function
20 minutes
24
Module 24 : DPRODUCT
20 minutes
25
Module 25: DSUM Function
20 minutes
26
Module 26: EDATE Function
20 minutes
27
Module 27: EOMONTH Function
20 minutes
28
Module 28: Find and Replace
20 minutes
29
Module 29: FIND Function
20 minutes
30
Module 30: INDEX Function
20 minutes
31
Module 31: INDEX MATCH Function
50 minutes
32
Module 32: INDIRECT Function
20 minutes
33
Module 33: Insert Serial Number Automatically
20 minutes
34
Module 34: IPMT Function
20 minutes
35
Module 35: ISBLANK Function
20 minutes
36
Module 36: ISERR Function
20 minutes
37
Module 37: ISLOGICAL Function
20 minutes
38
Module 38: ISNONTEXT Function
20 minutes
39
Module 39: ISNUMBER Function
20 minutes
40
Module 40: ISODD Function
20 minutes
41
Module 41: Isolate First Name
20 minutes
42
Module 42: ISTEXT Formula
20 minutes
43
Module 43: LEFT Function
20 minutes
44
Module 44: LEN Function
20 minutes
45
Module 45: MATCH Function
20 minutes
46
Module 46: Vlookup Multiple Results at Once
20 minutes
47
Module 47: OR Formula
20 minutes
48
Module 48: PMT Formula
20 minutes
49
Module 49: Replace Formula
20 minutes
50
Module 50: Select all Cells with Comments
20 minutes
51
Module 51: Sparkline Charts in Excel
52
Module 52: Straight Line Depreciation
20 minutes
53
Module 53: Strikethrough
20 minutes
54
Module 54: Sum of Years Depreciation
20 minutes
55
Module 55: Timeline in Pivot Table
20 minutes
56
Module 56: Vlookup with 2 Criteria
20 minutes
57
Module 57: Vlookup with DropDown List
20 minutes
58
Module 58: Vlookup with Max
20 minutes
59
Module 59: Vlookup with Min
20 minutes
60
Module 60: Watch Window In Excel
20 minutes




You've not yet made any notes for this course.

Frequently Asked Questions

To progress on to the next module of your course scroll to the bottom of the current module and press the “NEXT” button. This will take you directly to the following module.

Once you have completed the last module of the course please click on the button at the bottom of the module that says “START”. This will take you to the test.

The pass mark for the end of course test is 70%

Yes, you can re-take your test as many times as you need to.

No, once you have passed a test it is not possible to retake it.

When logging on to your course directly through our site you have lifetime access.

Click on the “My Profile” button at the top of any page on our site. This will take you to your profile page. Once there scroll down to the section headed “Edit Your Profile” and enter your new password.

Please click here to contact our student support team

If you would like to go back and view a completed module you can do this by clicking the “Course Modules” option from the side bar menu of your course. This will take you to a list of the modules contained within your course. Simply select the module you wish to study again.

Please click here to find out how to save your course modules as PDF files

Worksheets

Module 1: Add a Password

Module 1: Add a Password

Module 2 : AND Function

Module 2 : AND Function

Module 3 : Auto Updating Drop-down Lists

Module 3 : Auto Updating Drop-down Lists

Module 4 : CEILING Formula

Module 4 : CEILING Formula

Module 5 : Charts that Update Automatically

Module 5 : Charts that Update Automatically

Module 6 : CHOOSE Formula

Module 6 : CHOOSE Formula

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 8 : CORREL Function

Module 9 : Create an Advanced Calculator

Module 10 : Create a Combo Chart

Module 10 : Create a Combo Chart

Module 11 : Create a Custom Tab

Module 12 : Create a Pivot Chart

Module 12 : Create a Pivot Chart

Module 13 : Create Visual In-cell Indicators

Module 13 : Create Visual In-cell Indicators

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 15: Customize the Status Bar

Module 16: DATEDIF Function

Module 16: DATEDIF Function

Module 17: DATEVALUE Function

Module 17: DATEVALUE Function

Module 18: DAVERAGE Function

Module 18: DAVERAGE Function

Module 19 : DCOUNT Function

Module 19 : DCOUNT Function

Module 20 : DCOUNTA Function

Module 20 : DCOUNTA Function

Module 21 : Depreciation Formula

Module 21 : Depreciation Formula

Module 22 : DGET Function

Module 22 : DGET Function

Module 23 : DMAX Function

Module 23 : DMAX Function

Module 24 : DPRODUCT

Module 24 : DPRODUCT

Module 25: DSUM Function

Module 25: DSUM Function

Module 26: EDATE Function

Module 26: EDATE Function

Module 27: EOMONTH Function

Module 27: EOMONTH Function

Module 28: Find and Replace

Module 29: FIND Function

Module 29: FIND Function

Module 30: INDEX Function

Module 30: INDEX Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 31: INDEX MATCH Function

Module 32: INDIRECT Function

Module 32: INDIRECT Function

Module 33: Insert Serial Number Automatically

Module 33: Insert Serial Number Automatically

Module 34: IPMT Function

Module 34: IPMT Function

Module 35: ISBLANK Function

Module 35: ISBLANK Function

Module 36: ISERR Function

Module 36: ISERR Function

Module 37: ISLOGICAL Function

Module 37: ISLOGICAL Function

Module 38: ISNONTEXT Function

Module 38: ISNONTEXT Function

Module 39: ISNUMBER Function

Module 39: ISNUMBER Function

Module 40: ISODD Function

Module 40: ISODD Function

Module 41: Isolate First Name

Module 41: Isolate First Name

Module 42: ISTEXT Formula

Module 42: ISTEXT Formula

Module 43: LEFT Function

Module 43: LEFT Function

Module 44: LEN Function

Module 44: LEN Function

Module 45: MATCH Function

Module 45: MATCH Function

Module 46: Vlookup Multiple Results at Once

Module 46: Vlookup Multiple Results at Once

Module 47: OR Formula

Module 47: OR Formula

Module 48: PMT Formula

Module 48: PMT Formula

Module 49: Replace Formula

Module 49: Replace Formula

Module 50: Select all Cells with Comments

Module 51: Sparkline Charts in Excel

Module 51: Sparkline Charts in Excel

Module 52: Straight Line Depreciation

Module 52: Straight Line Depreciation

Module 53: Strikethrough

Module 53: Strikethrough

Module 54: Sum of Years Depreciation

Module 54: Sum of Years Depreciation

Module 56: Vlookup with 2 Criteria

Module 56: Vlookup with 2 Criteria

Module 57: Vlookup with DropDown List

Module 57: Vlookup with DropDown List

Module 58: Vlookup with Max

Module 58: Vlookup with Max

Module 59: Vlookup with Min

Module 59: Vlookup with Min

Module 60: Watch Window In Excel

Module PDF's

Module 1: Add a Password

Module 2 : AND Function

Module 3 : Auto Updating Drop-down Lists

Module 4 : CEILING Formula

Module 5 : Charts that Update Automatically

Module 6 : CHOOSE Formula

Module 7 : Compare 2 Lists with VLOOKUP Function

Module 8 : CORREL Function

Module 9 : Create an Advanced Calculator

Module 10 : Create a Combo Chart

Module 11 : Create a Custom Tab

Module 12 : Create a Pivot Chart

Module 13 : Create Visual In-cell Indicators

Module 14 : Create Visual In-cell Indicators with Icon Sets

Module 15: Customize the Status Bar

Module 16: DATEDIF Function

Module 17: DATEVALUE Function

Module 18: DAVERAGE Function

Module 19 : DCOUNT Function

Module 20 : DCOUNTA Function

Module 21 : Depreciation Formula

Module 22 : DGET Function

Module 23 : DMAX Function

Module 24 : DPRODUCT

Module 25: DSUM Function

Module 26: EDATE Function

Module 27: EOMONTH Function

Module 28: Find and Replace

Module 29: FIND Function

Module 30: INDEX Function

Module 31: INDEX MATCH Function

Module 32: INDIRECT Function

Module 33: Insert Serial Number Automatically

Module 34: IPMT Function

Module 35: ISBLANK Function

Module 36: ISERR Function

Module 37: ISLOGICAL Function

Module 38: ISNONTEXT Function

Module 39: ISNUMBER Function

Module 40: ISODD Function

Module 41: Isolate First Name

Module 42: ISTEXT Formula

Module 43: LEFT Function

Module 44: LEN Function

Module 45: MATCH Function

Module 46: Vlookup Multiple Results at Once

Module 47: OR Formula

Module 48: PMT Formula

Module 49: Replace Formula

Module 50: Select all Cells with Comments

Module 51: Sparkline Charts in Excel

Module 52: Straight Line Depreciation

Module 53: Strikethrough

Module 54: Sum of Years Depreciation

Module 55: Timeline in Pivot Table

Module 56: Vlookup with 2 Criteria

Module 57: Vlookup with DropDown List

Module 58: Vlookup with Max

Module 59: Vlookup with Min

Module 60: Watch Window In Excel

My Notes