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

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.

Where this comes up again

XLOOKUP Lookups
VLOOKUP Lookups
INDEX Lookups
MATCH Lookups

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

In E2, find which position Cal holds in the list of reps, with XMATCH.

To begin, type it exactly:

=XMATCH(D2,A2:A7)

Row	A	B	D	E
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

In E2, find which position Cal holds in the list of reps, with XMATCH. To begin, type it exactly: =XMATCH(D2,A2:A7).

Hint. Value, then the range.
2

A position is only useful when something reads it. In E3, find Ade's sales by feeding XMATCH's answer into INDEX over the sales column.

Hint. INDEX outside, XMATCH inside.
3

In E4, search the sales column, not the names, for the name in D2. Wrap the XMATCH in IFNA so a miss reads Not there.

Hint. IFNA outside, XMATCH inside.
4

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

Hint. Third argument 1.
5

In E7, write a formula using INDEX and XMATCH. It should give the name of the first rep at or above the target.

Hint. Same XMATCH, INDEX over column A.
6

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

Hint. Next smaller, not next larger.