How this formula works

Working from the inside out, MATCH is used to locate the position of the first match in the range B5:D5. The lookup_value is based on the value in B5 joined with an asterisk (*) as a wildcard, and match_type is set to zero to force an exact match:

This example shows how to use INDEX and MATCH to retrieve a grade from a table based a given score. This requires an "approximate match", since it is unlikely that the actual score exists in the table. The formula in cell F5 is: = INDEX ( C5:C9 ,...

This example shows how to use INDEX and MATCH to get information from a table based on an exact match. In the example shown, the formula in cell H6 is: = INDEX ( B5:E9 , MATCH ( H4 , B5:B9 , FALSE ), 2 ) which returns 1995, the year the movie Toy...

Case-sensitive lookup By default, standard lookups with VLOOKUP or INDEX + MATCH aren't case-sensitive. Both VLOOKUP and MATCH will simply return the first match, ignoring case. However, if you need to do a case-sensitive lookup, you can do so with...

To lookup in value in a table using both rows and columns, you can build a formula that does a two-way lookup with INDEX and MATCH. In the example shown, the formula in J8 is: = INDEX ( C6:G10 , MATCH ( J6 , B6:B10 , 1 ), MATCH ( J7 , C5:G5 , 1 ))...

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX...

In this video, we look at how the MATCH function can find the position of an item in a list. Once you have a position, you can use it to get related values with INDEX.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Key functions

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. Read more.