Excel track · Exercise 58 of 86 Paid

Lookup dashboard from three tables

Read a keyed value with INDEX and MATCH, then chain the pair to reach a third table.

Advanced

Four reps and a three-region target table beside them

Read-only data · screen 1 of 2 · answer in D2

In D2, read the sales figure of the rep named in F2.

	A	B	C	D	E	F	G
1	Rep	Region	Sales	Answer		Lookup
2	Ana	North	1200			Di
3	Bo	South	850
4	Cy	North	1450
5	Di	East	600

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In D2, report the target of the region the rep named in F2 belongs to.

	A	B	C	D	E	F	G
1	Rep	Region	Sales	Answer		Region	Target
2	Ana	North	1200			North	5000
3	Bo	South	850			South	3000
4	Cy	North	1450			East	2000
5	Di	East	600
6						Lookup
7						Di

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

INDEX reads a value at a row and column position, and MATCH supplies that position. Together they look up a key without counting columns. MATCH finds where the typed name sits in the names column, and INDEX reads the figure at that position in the sales column.

The first screen does that once, reading the sales figure for the name in F2. The second screen chains the pair twice, because a rep belongs to a region and the region holds the target. The inner pair finds Di's region, East, and the outer pair finds the target that goes with it.

The nesting fails loudly when the lists are confused. Point the outer MATCH at the rep names in place of the region list and it returns #N/A, because no rep name appears there. A helper column of regions does the same job with more cells to keep in step.

Syntax

=INDEX(answer,MATCH(name,names,0)) looks up anything; nest the inner INDEX for three tables

Worked example

On Four reps and a three-region target table beside them, =INDEX(C2:C5,MATCH(F2,A2:A5,0)) returns 600.

=INDEX(C2:C5,MATCH(F2,A2:A5,0))

Common errors

Error codes for INDEX and MATCH on the typed name, then nested through a region column
Error	What it means and how to fix it
`#N/A`	The typed name is not in the column MATCH reads, which leaves it no position to return.
`#REF!`	INDEX is given a position past the end of its range, which is what happens after a column is deleted.
`Wrong table`	The outer INDEX reads the target column at the rep's row instead of the region's row, and returns a nearby figure.

At work

A summary cell that follows a typed name through a rep list and a region table.