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

By Ali, Founder, Excel Lessons

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.

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

Names are in column D, salaries to the right: use VLOOKUP.

In B2, type:

=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

    Names are in column D, salaries to the right: use VLOOKUP. In B2, type: =VLOOKUP(B1,D2:F5,3,FALSE).

    Hint. D2:F5, column 3.

  2. 2

    In B3, find that person's department in column C. Column C sits 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, find the person's band from their grade. Shape: =VLOOKUP(VLOOKUP(lookup,table,col,FALSE),table,col,TRUE). The grade looks up exactly; the band looks up an approximate match.

    Hint. Exact match first, then an approximate one.

  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, find the total salary of the Ops department. Shape: =SUMIFS(range,range,value). A lookup cannot answer this; use SUMIFS instead.

    Hint. SUMIFS, not a lookup.