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.