Lessons Lesson 44

XMATCH: Position, Then Value

About this lesson

XMATCH returns the position of a value in a range, exact by default. It has the same match modes as XLOOKUP. INDEX reads whatever sits at that position in another range.

By the end you can

  • find a value's position with XMATCH and read another column at it.
  • find the first value at or above a target with match mode 1.

Practises XMATCH MATCH INDEX XLOOKUP

By Ali, Founder, Excel Lessons

The idea

MATCH with the trailing 0 forgotten is exact-by-default XMATCH. The position it returns is a number. A number can be handed to INDEX over any column of the same table. That is how a value found in one column reads a value from another.

Match mode 1 is the one to know beyond exact. On a sorted column it finds the first value at or above a target. The row where a threshold is crossed, the first bundle big enough, the first date after a deadline. XLOOKUP does the same in one call when you want the value. XMATCH is for when you want the row.

The mistake to watch for

Searching the wrong column. XMATCH for a name in the sales column returns #N/A, which is the true answer. IFNA turns it into words. And on a sorted column, match mode 1 and -1 answer opposite questions: first at or above, against last at or below. The wrong sign is off by one row on every line, without an error.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

XMATCH finds a value's position in a range.

In E2, type:

=XMATCH(D2,A2:A7)

E2
Row A B C D E F G
1 Rep Sales Look for Position
2 Bea 180 Cal
3 Gus 240 Ade
4 Cal 310
5 Ade 460 Target 500
6 Fay 520
7 Hal 700
8

Every step

  1. 1

    XMATCH finds a value's position in a range. In E2, type: =XMATCH(D2,A2:A7).

    Hint. Value, then the range.

  2. 2

    A position is only useful when something reads it. In E3, find Ade's sales with INDEX and XMATCH. Shape: =INDEX(range,XMATCH(lookup,range)).

    Hint. INDEX outside, XMATCH inside.

  3. 3

    In E4, search B2:B7 for D2's name. Shape: =IFNA(XMATCH(lookup,range),"text"). Show Not there when nothing matches.

    Hint. IFNA outside, XMATCH inside.

  4. 4

    The sales are sorted ascending. In E6, find the position of the first figure at or above E5. Use a match mode of 1.

    Hint. Third argument is 1.

  5. 5

    In E7, find the name of the first rep at or above E5.

    Hint. Same XMATCH, INDEX over column A.

  6. 6

    Someone wrote the threshold search with -1 instead of 1. Read =XMATCH(E5,B2:B7,-1) and say what position it returns.

    Hint. Next smaller, not next larger.