Lookups in Excel

Welcome

• this session is for 🌶🌶 intermediate Excel users
• we’ll get going properly at 13.05
• 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
• if you can’t access the chat, you might need to join our Teams channel: tinyurl.com/kindnetwork
• you can find session materials at tinyurl.com/kindtrp

The KIND network

• a social learning space for staff working with knowledge, information, and data across health, social care, and housing in Scotland
• we offer social support, free training, mentoring, community events, …
• Teams channel / mailing list

Excel training sessions

Session outline

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

Getting started

• files for today
  • s03_exercises.xlsx is a starting-point for the exercises today
  • s03_exercises_final.xlsx is the end-point for the exercises today - it’s there to help if you get stuck or lost

Introduction to lookups

• imagine that you have a table like this:
  
• how would you find someone’s age from their name?

INDEX() and MATCH()

• 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

INDEX()

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])

MATCH()

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

INDEX() + MATCH()

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

INDEX() + MATCH()

INDEX() + MATCH()

VLOOKUP()

• 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 more quirky than INDEX() + MATCH()

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()

VLOOKUP()

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)

XLOOKUP()

Returning several cells with XLOOKUP()

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))

Feedback and resources

• please can I ask for some feedback - takes less than a minute, completely anonymous, helps people like you find the right training for them