Excel: Looking up data continued

Last week, I showed you the simplest form of the XLOOKUP function. Though highly useful, it’s only really useful if you find exact matches. If you’re looking at an array of data – e.g. pay averages for age groups instead of specific ages – you need to help it a little. To this end, the function has a further three arguments. In order, the ones I’ve found useful are:

• if_not_found
  • Here you specify what to do if a valid match is not found
  • Defaults to #N/A
• match_mode
  • Specify the match type
  • 0: exact match – default setting which returns the error specified in if_not_found
  • -1: exact match OR the next smaller item
  • 1: exact match OR the next larger item

There are a few other variations, whose use has been documented by Microsoft.

My lookup table lists average pay in age groups, listed by starting age for the age group. The table is in a worksheet called “Average Pay“. The age group is in column A, the average pay in column C. The age of the person is question is in my calculations sheet, in column Z. My finished function for row 72 looks something like this:

=XLOOKUP(Z72,'Average Pay'!A:A,'Average Pay'!C:C,ERROR,-1). Translated to human terms, we’re looking for the value in cell Z72 in column A of the Average Pay Worksheet. We’re returning the value in column C for an exact match OR the next lower match. If no match is returned, we return “ERROR” instead.