1 of 4

Using XLOOKUP to join reports

Sid Devarenne

Western Washington University

devares@wwu.edu

2 of 4

Scenario

  • Goal:
    • List all books in subclass PS published 2019-2023
    • Include circulation statistics by year and by location at time of loan
    • Include Summit lending statistics
  • This requires reports in 3 different Analytics subject areas

3 of 4

XLOOKUP function

  • Search for a specified value in a column
  • Retrieve a value from the same row in a different column
  • Replaces VLOOKUP and HLOOKUP
  • Excel Help & Training - XLOOKUP

4 of 4

Combining reports with XLOOKUP

  • Basic XLOOKUP syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
    • lookup_value: The value to search for
    • lookup_array: The column(s) or row(s) to search
    • return_array: The column(s) or row(s) to return
    • [if_not_found]: (optional) Value to return when no match is found
  • Other optional arguments: [match_mode], [search_mode]