Excel Practice
Lessons Lesson 47

Choosing a Lookup: Putting It Together

About this lesson

Choosing a lookup comes down to three questions. Is the key unique? Does the answer sit to the right of it? Is the match exact or a band? A repeating key means a conditional function, not a lookup at all.

By the end you can

  • pick VLOOKUP, XLOOKUP, INDEX MATCH or an approximate match from the question.
  • recognise a repeating key and reach for SUMIFS instead of a lookup.

Practises VLOOKUP XLOOKUP INDEX MATCH SUMIFS

The idea

Every lookup in this course on one table. Unique key, answer to the right, exact: VLOOKUP, in any version of Excel. Answer to the left: XLOOKUP, or INDEX with MATCH. A number that falls into a band: an approximate match against a threshold table. A row and a column: INDEX with two MATCHes.

And the case that is not a lookup. A key that appears on more than one row has more than one answer. A lookup returns the first without comment. Ops salaries, March orders, every ticket for a customer: those are SUMIFS, COUNTIFS and FILTER questions. Recognising them is the last skill of the module.

The mistake to watch for

A lookup on a key that repeats. It returns the first match and never says there was a second. So the Ops salary is one person's, and the department's total is somewhere else. When the question is really about every row that matches, it is SUMIFS, COUNTIFS or FILTER. Recognising that is most of choosing.

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

In B2, the salary of the person named in B1. The names are in column D and the salaries to their right, so use VLOOKUP with an exact match.

To begin, type it exactly:

=VLOOKUP(B1,D2:F5,3,FALSE)

B2
Row A B C D E F G
1 Who Cai Dept Name Grade Salary
2 Salary Ops Ada 3 34000
3 Dept Sales Ben 2 29500
4 Band Ops Cai 4 41000
5 Ops total Finance Dee 3 36000
6
7 Grade from Band
8 1 Junior
9 3 Senior
10 5 Lead

Every step

  1. 1

    In B2, the salary of the person named in B1. The names are in column D and the salaries to their right, so use VLOOKUP with an exact match. To begin, type it exactly: =VLOOKUP(B1,D2:F5,3,FALSE).

    Hint. D2:F5, column 3.

  2. 2

    In B3, that person's department, which is in column C, to the left of the names. Use XLOOKUP.

    Hint. Search D, return C.

  3. 3

    Grades map to bands by the thresholds in C8:D10. In B4, the person's band from their grade, with an approximate match on the threshold table.

    Hint. Exact inside, approximate outside.

  4. 4

    Ops has two people. Read =VLOOKUP("Ops",C2:F5,4,FALSE) and say what it returns.

    Hint. First Ops row.

  5. 5

    In B5, write a formula using SUMIFS that gives the total salary of the Ops department. That is the question step 4's lookup could not answer.

    Hint. SUMIFS, not a lookup.