Elevating your lookup game!

Advanced Excel Functions

Agata Bak-Geerinck

Product Owner Data, Telenet

From Excel - to leading a data team!

white space Picture of the instructor Agata

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

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

¹ https://support.microsoft.com/

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

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

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

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

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.

Time for practice!

Advanced Excel Functions