Lookups in Excel

excel
intermediate
Published

August 1, 2024

Session materials

Previous attendees have said…

  • 16 previous attendees have left feedback
  • 100% would recommend this session to a colleague
  • 100% said that this session was pitched correctly

Three random comments from previous attendees
  • Very helpful thanks
  • Really useful, hands on session. It would be great to be able to download a second version of the workbook that included all the correct formulas so we could check our work later without having to pester Brendan.
  • Very helpful!

Welcome

  • 🌶🌶 this session is designed for intermediate Excel users
  • you’ll need Excel M365 Desktop and this sample Excel workbook to follow along, and you’ll need to be comfortable working with tables, references, and formulas to get the most out of the session
  • this session is part of our Excel Skills course, which is a five-session live-on-Teams training programme designed to build your Excel skills to help you work more effectively in Excel

Session outline

  • introduction to lookups
  • three methods for lookups
    • INDEX() + MATCH()
    • VLOOKUP()
    • XLOOKUP()
  • partial matching

Introducing lookups

What does a lookup do? Imagine that you have a table like this:

A lookup would let us find someone’s age from their name by:

  1. taking that name from somewhere
  2. finding out what row of the table that name is found in
  3. and then returning the age from that row

INDEX() and MATCH()

  • MATCH() takes a value, and finds what row it occurs in
  • INDEX() takes a row, and returns a value from it
  • older way of doing lookups
    • think timeless classic rather than old banger
  • simple and flexible
  • great way to understand and practice the logic of lookups

INDEX()

  • INDEX() retrieves data from cells. You supply two arguments:
    • an array (range) of cells
    • a row number
  • INDEX(A1:A10, 3) gives you the third row of the range A1:A10
  • general syntax: INDEX(array, row_num, [column_num])
    • array is the range we want to look in
    • row_num and the optional column_num is the number of the row (± column) that you want
Task
  1. Find the INDEX worksheet of the sample workbook
  2. We have a one-column table containing names in A2:A11
  3. Use INDEX() to find the value in the third row

MATCH()

  • MATCH() retrieves the row number of a cell. You supply two arguments:
    • a lookup value
    • an array of cells
  • MATCH(lookup_value, lookup_array, [match_type])
Warning
  • the optional match_type argument is deathly important:
  • 1 (or omitted) finds the largest value that is less than or equal to the lookup
  • 0 finds the first value that is exactly equal to the lookup
  • -1 finds the smallest value that is greater than or equal to the lookup

MATCH()

Task
  1. Go to the MATCH worksheet
  2. We have (the same) one-column table of names in A2:A11
  3. Use MATCH() to find the row containing “Broward” - being careful to set match_type appropriately

INDEX() + MATCH()

  • given that match gives us the row number, and index takes a row number and returns a value, we can link them up nicely

  • first search through our data to find a MATCH()ing row

  • then use INDEX() to retrieve some other column from that row

  • the general syntax will be =INDEX(range, MATCH("Name", range, 0))

Task
  1. Go to the INDEX + MATCH (1) worksheet
  2. We have a two column table, and we want a user to be able to supply a number in E2, and receive the appropriate word in E3
  3. First, write an appropriate MATCH() formula to find the row matching the input
  4. Next, write an INDEX() to take that MATCH() value and return the appropriate word
  5. Finally, try joining both formulas together in one to give an INDEX() + MATCH()

Task
  1. Go to the INDEX + MATCH (2) worksheet. We have several formulas about Eunika. Can you fix them to give the correct result?

VLOOKUP()

  • while INDEX() and MATCH() is great, it does produce some nasty-looking formulas
  • VLOOKUP() was introduces as a neater alternative - roughly INDEX() + MATCH() in one
  • VLOOKUP returns along rows, allowing us to look up a value in one column, and returns a corresponding value from another column
  • that sounds ideal, but beware: VLOOKUP() is much more quirky and unreliable than INDEX() + MATCH()
  • here’s the syntax for VLOOKUP():
    • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
      • lookup_value is what we want to look up
      • table_array is the table that we’re looking up in
      • col_index_num is the number of the column that we want to return from
      • range_lookup allows either approximate matching (the default) or exact matching (by specifying FALSE)
        • The major source of oddity. Make sure you check the manual before doing anything serious with VLOOKUP()
Task
  1. Go to the VLOOKUP worksheet
  2. There are two requests for lookups. Can you populate them with appropriate VLOOKUP() formulas?
Warning

XLOOKUP()

  • semi-new (any versions after Excel 2019)
  • similar to INDEX() + MATCH(). Major differences:
    • it spills, so ideal for returning unpredictable quantities of cells
    • provides exact matching by default
  • the basic syntax is: = XLOOKUP(lookup_value, lookup_array, return_array)
Task
  1. Go to the XLOOKUP (1) worksheet
  2. There are two requests for lookups. Can you populate them with appropriate XLOOKUP() formulas?

Returning several cells with XLOOKUP()

Task
  1. Go to the XLOOKUP (2) worksheet
  2. Set up an XLOOKUP() supplying a range of columns as the return_array value

Fighting with tables

  • note that returning several cells into a table often causes errors
  • you may need to work with a range instead if planning to use XLOOKUP() to return an unpredictable amount of data

Partial text matching

  • Excel is not brilliant for partial text matching
  • there are a number of commercial add-ins and VBA-based solutions
    • those come with risks
    • we’ll concentrate on base-Excel here
  • three main options:
  1. XLOOKUP() with match_mode = 2 + wildcard
  2. VLOOKUP() with range_lookup = FALSE + wildcards
  3. MATCH() with match_type = 0 + wildcards

XLOOKUP() with match_mode = 2 + wildcard

  • XLOOKUP() allows partial matching via the “*” wildcard
  • add “*” to the end of the partial string you want to match
  • however, please be aware that:
  1. this will only reliably match sub-strings at the start of strings
    1. (so “ow*” will match “owl” but not “towel”)
    2. adding extra wildcards interacted unpredictably with match_mode in testing
  • =XLOOKUP("*substring*", search col, return col, ,2)

VLOOKUP() with range_lookup = FALSE + wildcards

  • as with XLOOKUP, adding “*” allows wildcard matching
  • but you can do better with the wildcard sandwich: "*substring*"
    • or, if you’re bringing in a reference "*" & cell_ref & "*"
  • ensure you set the (confusingly named) range_lookup argument to FALSE to allow approximate matching
  • VLOOKUP("*substring*", table, column, FALSE)

MATCH() with match_type = 0 + wildcards

  • same wildcard sandwich strategy, with the flexibility of INDEX() + MATCH()
  • my preferred solution, largely because the other options are harder to debug
  • =INDEX(array, MATCH("*substring*", array, 0))
Task
  1. Go to the Partial matching worksheet
  2. Try implementing these three ways of partial matching