Feeding a chart from a selected cell with INDEX and MATCH

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Feeding a chart from a selected cell with INDEX and MATCH

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula finds the row number of the item chosen in selector cell A13 within A2:A10?
A =MATCH(A13,A2:A10,0)
B =MATCH(A2:A10,A13,0)
2. What does the 0 at the end of =MATCH(A13,A2:A10,0) do?
A It asks for an exact match
B It returns the first row of the list
3. In =INDEX(B2:M10,row,0), what does the 0 for the column argument tell INDEX to do?
A Return the whole row
B Return the first column only
4. Why does the chart point to the small helper range instead of the full table?
A The helper range makes the full table smaller and faster to load
B The helper range changes when the selector changes, so the chart updates
5. Which formula correctly pulls the whole row for the item in A13 into the helper range?
A =INDEX(A2:A10,MATCH(A13,B2:M10,0),0)
B =INDEX(B2:M10,MATCH(A13,A2:A10,0),0)
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1=MATCH(A13,A2:A10,0) AChart data range: B13:M13
2=INDEX(B2:M10,MATCH(A13,A2:A10,0),0) BThe match type is 1, which expects sorted data and may return a wrong row. It should be 0 for an exact match.
3Use XLOOKUP as an alternative that returns the whole row of values for the selector item C=XLOOKUP(A13,A2:A10,B2:M10)
4Point the chart at the helper range that holds the selected row, not at the full table DA13 holds the item name, not a row number. INDEX needs MATCH(A13,A2:A10,0) to find the row number.
5=MATCH(A13,A2:A10,1) EFinds which row of the list holds the item chosen in the selector cell A13. The 0 means exact match.
6=INDEX(B2:M10,A13,0) FReturns the whole row of data for the selected item so a chart can plot it from a helper range.
C

Definitions

=INDEX(B2:M10,MATCH(A13,A2:A10,0),0)
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
=_____(B2:M10,MATCH(A13,A2:A10,0),0)
2.
=INDEX(B2:M10,_____(A13,A2:A10,0),0)
3.
=INDEX(B2:M10,MATCH(A13,A2:A10,_),0)
4.
=INDEX(B2:M10,MATCH(A13,A2:A10,0),_)
5.
=_______(A13,A2:A10,B2:M10)
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A B13: =MATCH(A13,A2:A10,1)
B B14: =INDEX(B2:M10,B13,0)
C Chart plots B14:M14
Question 2
A A13: Spain
B B13: =MATCH(A13,A2:A10,0)
C B14: =INDEX(B2:M10,B13,1)
Question 3
A A13: Spain
B B13: =MATCH(A13,A2:A10,0)
C B14: =INDEX(B2:M10,A13,0)
Question 4
A A13: Spain
B B13: =MATCH(A2:A10,A13,0)
C B14: =INDEX(B2:M10,B13,0)
Question 5
A A13: Spain
B B13: =MATCH(A13,A1:A10,0)
C B14: =INDEX(B2:M10,B13,0)

Answer Key

Feeding a chart from a selected cell with INDEX and MATCH
Section A: Quick Questions
Q1
A — =MATCH(A13,A2:A10,0)
Q2
A — It asks for an exact match
Q3
A — Return the whole row
Q4
B — The helper range changes when the selector changes, so the chart updates
Q5
B — =INDEX(B2:M10,MATCH(A13,A2:A10,0),0)
Section B: Match the Pairs
Q1
=MATCH(A13,A2:A10,0) → Finds which row of the list holds the item chosen in the selector cell A13. The 0 means exact match.
Q2
=INDEX(B2:M10,MATCH(A13,A2:A10,0),0) → Returns the whole row of data for the selected item so a chart can plot it from a helper range.
Q3
Use XLOOKUP as an alternative that returns the whole row of values for the selector item → =XLOOKUP(A13,A2:A10,B2:M10)
Q4
Point the chart at the helper range that holds the selected row, not at the full table → Chart data range: B13:M13
Q5
=MATCH(A13,A2:A10,1) → The match type is 1, which expects sorted data and may return a wrong row. It should be 0 for an exact match.
Q6
=INDEX(B2:M10,A13,0) → A13 holds the item name, not a row number. INDEX needs MATCH(A13,A2:A10,0) to find the row number.
Section C: Definitions
Q1
=INDEX(B2:M10,MATCH(A13,A2:A10,0),0) — MATCH finds the row of the item chosen in the selector cell A13. INDEX returns that whole row into a helper range, and the chart plots the helper range, so it updates when the choice changes.
Section D: Fill in the Blanks
Q1
=INDEX(B2:M10,MATCH(A13,A2:A10,0),0)
Q2
=INDEX(B2:M10,MATCH(A13,A2:A10,0),0)
Q3
=INDEX(B2:M10,MATCH(A13,A2:A10,0),0)
Q4
=INDEX(B2:M10,MATCH(A13,A2:A10,0),0)
Q5
=XLOOKUP(A13,A2:A10,B2:M10)
Section E: Spot the Error
Q1
A — The last MATCH argument must be 0 for an exact match. Type 1 does an approximate match on sorted data and can return the wrong row for names. It should be =MATCH(A13,A2:A10,0).
Q2
C — The column number 1 returns only one value from column B. Using 0 as the column number returns the whole row for the chart. It should be =INDEX(B2:M10,B13,0).
Q3
C — INDEX needs a row number, but A13 holds the text Spain, which causes an error. It should use B13, the row number that MATCH found.
Q4
B — The MATCH arguments are the wrong way round. The first is the value to find (A13) and the second is the list to search (A2:A10). It should be =MATCH(A13,A2:A10,0).
Q5
B — The lookup range A1:A10 includes the header row, so the row number is one too big and INDEX returns the next item's data. It should be A2:A10 to match B2:M10.