INDEX MATCH in Excel: Rank & Score Guide

This guide explains how to use Excel’s INDEX and MATCH functions to rank and score data, and how to combine them with the RANK function for dynamic, lookup-based scoring. You’ll learn the formulas, see a worked example, and get answers to common questions about these powerful functions.

Formula

The core formula combines three functions:

• RANK(score, list, order) – returns the rank of a score within a list. Use 0 for descending (highest score = rank 1) or 1 for ascending (lowest = rank 1).
• INDEX(array, row_num) – returns the value at a given position in an array.
• MATCH(lookup_value, lookup_array, match_type) – returns the position of a value in an array. Use 0 for exact match, -1 for approximate match (largest value ≤ lookup), 1 for approximate (smallest value ≥ lookup).

In the calculator above, the rank is computed directly with RANK. The grade uses INDEX and MATCH: MATCH finds where the score falls in the grade boundaries, and INDEX returns the corresponding letter.

Worked Example

Using the default values: score = 85, list = {90, 80, 85, 70, 95}.

1. Rank: RANK(85, {90,80,85,70,95}, 0) → 85 is the 3rd highest (after 95 and 90). So rank = 3.
2. Percentile: (1 – (3-1)/5) * 100 = (1 – 0.4) * 100 = 60%. The score is higher than 60% of the list.
3. Grade: MATCH(85, {90,80,70,60,0}, -1) → 85 is between 80 and 90, so MATCH returns 2 (the position of 80). INDEX({“A”,”B”,”C”,”D”,”F”}, 2) → “B”.

The calculator shows rank = 3, percentile = 60.0%, grade = B.

How INDEX and MATCH Work Together

INDEX and MATCH are often used together as a more flexible alternative to VLOOKUP. MATCH finds the row or column position, and INDEX returns the value at that position. This combination allows leftward lookups, dynamic column selection, and works with unsorted data when using exact match.

For ranking specifically, you can use MATCH to find where a score sits in a sorted list, then INDEX to pull a label or score from another column. For example, to assign a rank label (e.g., “Top”, “Middle”, “Bottom”), you could use INDEX with MATCH on percentile thresholds.

How to Choose the Right Match Type

• Exact match (0) – use when you need an exact ID, name, or code. The lookup array does not need to be sorted.
• Approximate match (-1) – use for grade boundaries, tax brackets, or any threshold where you want the largest value that is less than or equal to the lookup. The lookup array must be sorted descending.
• Approximate match (1) – use for the smallest value greater than or equal to the lookup. The array must be sorted ascending.

For ranking, you usually use RANK directly, but INDEX/MATCH becomes useful when you need to map a rank or score to a category.

Common Problems and Mistakes

• Wrong match type – using 0 when you need -1 or 1. This causes #N/A errors.
• Unsorted array for approximate match – approximate match requires the array to be sorted in the correct order, otherwise results are unpredictable.
• Rank with ties – RANK gives the same rank to duplicates, but then skips the next rank. For example, two scores of 85 both get rank 3, and the next rank is 5. Use RANK.EQ for this behavior, or RANK.AVG for average ranks.
• Array size mismatch – INDEX and MATCH arrays must be the same size, otherwise you get #VALUE! or #N/A.
• Hard-coded arrays – in the calculator, the grade boundaries are hard-coded. In Excel, you would typically reference a range of cells instead.

Comparison: INDEX/MATCH vs VLOOKUP

INDEX/MATCH is generally more flexible than VLOOKUP:

• It can look to the left (VLOOKUP can only look right).
• It does not break when you insert or delete columns.
• It is faster on large datasets because it only evaluates the necessary ranges.
• It can return a value from any column, not just the first.

However, VLOOKUP is simpler for beginners and works fine for small, static tables. For ranking and scoring, INDEX/MATCH is often combined with RANK to create dynamic scorecards.

Costs and Alternatives

INDEX, MATCH, and RANK are built-in Excel functions – no additional cost. They are available in Excel 2010 and later, including Microsoft 365 and Excel Online. Alternatives include:

• XLOOKUP (Excel 365) – a modern replacement that handles both exact and approximate matches with fewer arguments.
• VLOOKUP – simpler but limited.
• Power Query – for heavy data transformation, but overkill for simple ranking.
• Array formulas – for complex scoring, but harder to debug.

For scoring, you could also use the PERCENTRANK function or NORM.DIST for statistical scoring, but RANK+INDEX/MATCH remains the most straightforward for most users.

Frequently Asked Questions

Why does my INDEX/MATCH return #N/A?

#N/A usually means the MATCH function could not find the lookup value. Check the match type – if you are using approximate match, ensure the array is sorted correctly. Also verify that the lookup value exists in the array when using exact match.

Can I use INDEX/MATCH to rank without sorting?

Yes, RANK does not require sorting. If you need to assign a label based on rank, you can use RANK to get the position, then INDEX on a sorted list of labels. For example, to assign “Top 10%” you could use IF(rank <= COUNT(list)*0.1, "Top 10%", "Other").

How do I handle ties in ranking?

Use RANK.EQ for standard competition ranking (ties get same rank, next rank skipped). Use RANK.AVG to give tied scores the average rank. Both are available in Excel 2010 and later.

What is the difference between RANK and PERCENTRANK?

RANK gives an integer position (1, 2, 3...). PERCENTRANK gives the percentile as a decimal (0.0 to 1.0). For example, a score that is 3rd out of 5 has a rank of 3 and a percentile of 0.6 (60%).

Can I use INDEX/MATCH with multiple criteria?

Yes, you can combine multiple MATCH functions using array formulas or by using the & operator to concatenate criteria. For example, INDEX(data, MATCH(1, (A:A="X")*(B:B="Y"), 0)) entered as an array formula (Ctrl+Shift+Enter in older Excel).

Is INDEX/MATCH faster than VLOOKUP?

Generally yes, especially on large datasets, because INDEX/MATCH only looks at the specific rows and columns needed. VLOOKUP scans the entire table. The difference is noticeable on tens of thousands of rows.

Related

• Board Oversight of AI: FAQs
• Tree Sitter for AI Agents: Which Code Changes Matter?
• Decimal to Percentage: How to Convert & Examples

in score | Score | points | 85 | 0
in list | Scores (comma-separated) | 90, 80, 85, 70, 95 | 0
out rank | Rank (1=highest) | - | RANK(score, list, 0) | 0
out pct | Percentile | % | (1 - (rank - 1) / count(list)) * 100 | 1
out grade | Grade | - | INDEX({"A","B","C","D","F"}, MATCH(score, {90,80,70,60,0}, -1)) | 0