Lookup & Reference Functions
Master 14 Excel lookup and reference functions like VLOOKUP, XLOOKUP, INDEX, MATCH, and OFFSET with interactive exercises. Retrieve data and connect datasets efficiently.
Live Score Feedback
Active Logic Validation
Industry Datasets
Lookup & Reference Functions Foundations
Learn how Excel lookup and reference functions search tables, return matching values, and navigate the worksheet grid.
Address Function
Creates a cell reference as text, based on a given row and column number. Master dynamic referencing with ADDRESS + INDIRECT.
Choose Function
Select a value or reference from a list based on an index number. CHOOSE is useful for fixed option lists, scenario switches, and simple mapping formulas.
Column Function
Return the column number of a cell reference. The essential tool for dynamic VLOOKUP indices, auto-adjusting horizontal models, and position-aware grid mapping.
Columns Function
Count how many columns are in a range or array. COLUMNS is useful for measuring width, building horizontal counters, and checking table limits.
Formulatext Function
Return the formula from a cell as text. FORMULATEXT is useful for audits, documentation, and checking whether formulas are consistent across a sheet.
Hlookup Function
Search for a value in the top row of a table and return a value from a specified row in the same column. HLOOKUP is useful when headers run across the top instead of down the left side.
Hyperlink Function
Create a clickable link to a web page, file, email address, or cell location. HYPERLINK is useful for navigation, file access, and interactive workbook layouts.
Index Function
Return a value from a specific row and column inside a range. INDEX is useful for flexible lookups, matrix retrieval, and dynamic ranges.
Match Function
Return the relative position of a value in a row or column. MATCH is useful for INDEX/MATCH formulas, header lookups, and existence checks.
Offset Function
Return a reference that is a specified number of rows and columns away from a starting cell or range. OFFSET is useful for dynamic ranges, rolling calculations, and reference shifting.
Row Function
Return the row number of a cell or range reference. ROW is useful for numbering, relative offsets, and formulas that need to know their vertical position.
Rows Function
Count how many rows are in a range or array. ROWS is useful for measuring range size, building counters, and checking table limits.
Vlookup Function
Retrieve data from a table by searching for a key in the first column. VLOOKUP is useful for classic vertical lookup tables such as IDs, prices, and tax brackets.
Xlookup Function
Look up a value in one range and return a corresponding value from another range. XLOOKUP is a flexible modern replacement for many VLOOKUP, HLOOKUP, and INDEX/MATCH patterns.
"The lookup path is the core of data relationality. We move beyond simple VLOOKUP to master XLOOKUP and dynamic array referencing, enabling you to build scalable, interconnected data models with zero manual friction."
"You'll learn to handle complex data retrieval scenarios, including multi-criteria lookups and horizontal referencing, transforming how you manage and link disparate datasets."