Advanced Excel Functions
Agata Bak-Geerinck
Product Owner Data, Telenet
Previously on DataCamp:
VLOOKUP()
- vertical lookupHLOOKUP()
- horizontal lookupLimitations:
VLOOKUP()
- searched value must be to the right of the lookup valueHLOOKUP()
- searched value must be below of the lookup valueArray - a set of row or column of values, or a combination of rows and columns of values$^1$
An Excel table consists of:
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])
Labels Sales for April? = INDEX ( B2:D4, 2, 2)
INDEX(array, row_num, [column_num])
MATCH(lookup_value, lookup_array, [match_type])
Where to find Labels Sales for April?
= Match ( "Labels", Categories, 0)
= row 2
= Match ( "APR", Months, 0)
= column 2
= INDEX ( B2:D4, 2, 2)
= INDEX(array, MATCH(rows), MATCH(columns))
= INDEX ( Sales, MATCH( "Labels", Categories, 0), MATCH( "APR", Months, 0) )
Commercial dataset:
Data at a glance:
Check out the Metadata sheet for more info.
Advanced Excel Functions