Elevating your lookup game!

Advanced Excel Functions

Agata Bak-Geerinck

Product Owner Data, Telenet

From Excel - to leading a data team!

white spacePicture of the instructor Agata

Advanced Excel Functions

The power of LOOKUP functions

Previously on DataCamp:

  • VLOOKUP() - vertical lookup
  • HLOOKUP() - horizontal lookup

white space

Limitations:

  • VLOOKUP() - searched value must be to the right of the lookup value
  • HLOOKUP() - searched value must be below of the lookup value

white space

A table with various rows and columns and once cell highlighted in yellow

white space

A table with various rows and columns and once cell highlighted in yellow

Advanced Excel Functions

What is an array?

Array - a set of row or column of values, or a combination of rows and columns of values$^1$

An example of an array

An Excel table consists of:

  • Header: row and column names
  • Array: set of underlying values
1 https://support.microsoft.com/
Advanced Excel Functions

Drum roll... XLOOKUP!

XLOOKUP() - A lookup function that can search in any direction, thanks to arrays.

NEW as of Excel 2021!

Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

An example of XLOOKUP function

XLOOKUP function with name ranges

Advanced Excel Functions

2D lookups? INDEX()

A Table with 4 columns and 3 rows, to illustrate INDEX function use

white space

Labels Sales for April? = INDEX ( B2:D4, 2, 2)

white space

  • Syntax: INDEX(array, row_num, [column_num])
  • Returns the value of a cell within a specified array
Advanced Excel Functions

2D lookups? MATCH()

  • Syntax: MATCH(lookup_value, lookup_array, [match_type])
  • Finds which row and column to reference

A Table in Excel with 5 columns and 4 rows, to illustrate INDEX and MATCH function use

Where to find Labels Sales for April?

= Match ( "Labels", Categories, 0) = row 2

= Match ( "APR", Months, 0) = column 2

Advanced Excel Functions

2D lookups? (INDEX and) MATCH made in heaven

A Table in Excel with 5 columns and 4 rows, to illustrate INDEX and MATCH function use

= INDEX ( B2:D4, 2, 2)

= INDEX(array, MATCH(rows), MATCH(columns))

= INDEX ( Sales, MATCH( "Labels", Categories, 0), MATCH( "APR", Months, 0) )

Advanced Excel Functions

Meet our dataset!

Commercial dataset:

Visual explanation of the dataset: USA contour, icon of a shopping basket and of a stack of coins

Data at a glance:

  • Order information
  • Customer socio-demo
  • Detailed product data
  • Sales, Volumes, Discounts, and Profits

white space

Check out the Metadata sheet for more info.

Advanced Excel Functions

Time for practice!

Advanced Excel Functions

Preparing Video For Download...