Lookup & Reference Excel Functions

Find and retrieve data from tables and ranges

Lookup functions ranked by use case

  • One value from a table → [VLOOKUP](/functions/vlookup/) or [XLOOKUP](/functions/xlookup/)
  • Left lookup or custom not-found → [XLOOKUP](/functions/xlookup/)
  • Two-way lookup → [INDEX](/functions/index/) + [MATCH](/functions/match/)
  • All matching rows → [FILTER](/functions/filter/)
  • Position only → [MATCH](/functions/match/) or [XMATCH](/functions/xmatch/)

Functions in this category

  • XLOOKUP: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • VLOOKUP: Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
  • INDEX: Returns the value of an element in a table or array, selected by the row and column number indexes.
  • MATCH: Returns the relative position of an item in an array that matches a specified value.
  • HLOOKUP: Searches for a value in the top row of a table and returns a value in the same column from a row you specify.
  • XMATCH: Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching.
  • INDIRECT: Returns the reference specified by a text string. References are immediately evaluated to display their contents.
  • OFFSET: Returns a reference offset from a starting point by a specified number of rows and columns.
  • CHOOSE: Returns a value from a list of values based on an index number.
  • FILTER: Filters a range of data based on criteria you define. Returns an array of values that meet the specified conditions.
  • SORT: Sorts the contents of a range or array in ascending or descending order.
  • SORTBY: Sorts the contents of a range or array based on the values in a corresponding range or array.
  • UNIQUE: Returns a list of unique values from a range or array.
  • TRANSPOSE: Converts a vertical range to horizontal, or vice versa.
  • ROW: Returns the row number of a reference.
  • ROWS: Returns the number of rows in a reference or array.
  • COLUMN: Returns the column number of a reference.
  • COLUMNS: Returns the number of columns in a reference or array.
  • ADDRESS: Creates a cell reference as text from row and column numbers.
  • HYPERLINK: Creates a clickable hyperlink to a URL, file, or location within the workbook.
  • LOOKUP: Looks up a value in a one-row or one-column range and returns a value from the same position in another range.
  • WRAPROWS: Wraps a row or column of values into a 2D array after a specified number of elements.
  • WRAPCOLS: Wraps a row or column of values into a 2D array after a specified number of elements by column.
  • TOROW: Returns an array as a single row.
  • TOCOL: Returns an array as a single column.
  • TAKE: Returns a specified number of rows or columns from the start or end of an array.
  • DROP: Excludes a specified number of rows or columns from the start or end of an array.
  • CHOOSEROWS: Returns the specified rows from an array.
  • CHOOSECOLS: Returns the specified columns from an array.
  • EXPAND: Expands or pads an array to specified row and column dimensions.
  • VSTACK: Appends arrays vertically (stacks rows).
  • HSTACK: Appends arrays horizontally (stacks columns).
  • GETPIVOTDATA: Returns data stored in a PivotTable report.
  • GROUPBY: Groups data by row values and aggregates associated values.
  • PIVOTBY: Creates a pivot table-like summary with row and column groupings.
  • AREAS: Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell.
  • RTD: Retrieves real-time data from a program that supports COM automation.
  • DROPDOWN: Creates a dropdown list in a cell allowing users to select from predefined options. This is an Excel 365 feature.

Mastering Lookup & Reference functions end-to-end

  • Find and retrieve data from tables and ranges — use this category page as a map before diving into individual function syntax pages.
  • Functions indexed here: XLOOKUP, VLOOKUP, INDEX, MATCH, HLOOKUP, XMATCH, INDIRECT, OFFSET, CHOOSE, FILTER, SORT, SORTBY and more.
  • Pick one function per week for your team, copy the examples into a practice workbook, and spot-check against manual math.
  • When functions in this category feed lookups or aggregates, read the related comparison and problem guides linked from function pages.
  • Small categories still matter in specialized models — document which niche functions your template depends on for auditors.
  • Confirm Excel version requirements: dynamic array and cube functions may not open in Excel 2019 even if other category members do.

Category troubleshooting patterns

  • #NAME? often means a niche function name typo or a locale mismatch — compare spelling to this English directory.
  • #VALUE! in specialized functions usually means wrong argument type — review each parameter description on the function page.
  • When results differ from documentation, verify regional settings, unit assumptions, and whether the workbook opened in compatibility mode.
  • Cross-link to fix-excel-formula-errors when multiple function types in this category fail after a bulk find-replace.

Function deep dives in this category

  • XLOOKUP (/functions/xlookup/): Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
  • VLOOKUP (/functions/vlookup/): Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
  • INDEX (/functions/index/): Returns the value of an element in a table or array, selected by the row and column number indexes. Syntax: =INDEX(array, row_num, [column_num]).
  • MATCH (/functions/match/): Returns the relative position of an item in an array that matches a specified value. Syntax: =MATCH(lookup_value, lookup_array, [match_type]).
  • HLOOKUP (/functions/hlookup/): Searches for a value in the top row of a table and returns a value in the same column from a row you specify. Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
  • XMATCH (/functions/xmatch/): Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching. Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]).
  • INDIRECT (/functions/indirect/): Returns the reference specified by a text string. References are immediately evaluated to display their contents. Syntax: =INDIRECT(ref_text, [a1]).
  • OFFSET (/functions/offset/): Returns a reference offset from a starting point by a specified number of rows and columns. Syntax: =OFFSET(reference, rows, cols, [height], [width]).