What this category helps you do
Lookup formulas connect tables together. Dynamic array formulas go further by returning lists, tables, and spill ranges that update automatically when the source data changes.
- Return a price from a product table
- Find an employee name from an ID
- Filter open orders into a report
- Create sorted or unique lists
Lookup & Dynamic Arrays formula examples
Choose a formula below. Each page explains the formula in plain English and gives examples one at a time so long formulas are easier to read and copy.
CHOOSECOLS
returns selected columns from an array by position.
Open examples →FormulaCHOOSEROWS
returns selected rows from an array by position.
Open examples →FormulaDROP
removes a number of rows or columns from the start or end of a range.
Open examples →FormulaFILTER
returns only the rows or columns that meet a condition.
Open examples →FormulaHLOOKUP
searches the first row of a table and returns a value from a lower row.
Open examples →FormulaHSTACK
combines ranges horizontally into one wider array.
Open examples →FormulaINDEX
returns the value at a specific row and column position inside a range.
Open examples →FormulaINDEX MATCH
uses MATCH to find a position and INDEX to return the value at that position.
Open examples →FormulaINDIRECT
turns a text reference into a real cell or range reference.
Open examples →FormulaLOOKUP
returns a value from a one-row or one-column range based on an approximate match.
Open examples →FormulaMATCH
returns the position of a value inside a row or column.
Open examples →FormulaOFFSET
returns a range that is a set number of rows and columns away from a starting cell.
Open examples →FormulaSORT
sorts a range by one or more columns.
Open examples →FormulaSORTBY
sorts one range based on the values in another range.
Open examples →FormulaTAKE
returns the first or last rows or columns from a range.
Open examples →FormulaTRANSPOSE
turns rows into columns or columns into rows.
Open examples →FormulaUNIQUE
returns a list of distinct values from a range.
Open examples →FormulaVLOOKUP
searches the first column of a table and returns a value from a column to the right.
Open examples →FormulaVSTACK
combines ranges vertically into one longer array.
Open examples →FormulaXLOOKUP
looks for a value in one column or row and returns the matching value from another column or row.
Open examples →FormulaXMATCH
returns the position of a value with more match and search options than MATCH.
Open examples →Best formulas to start with
How to learn this category
- Open one formula page and test the first beginner example.
- Change one cell reference at a time so you understand what each part controls.
- Check the common mistakes section before copying the formula down.
- Move to related formulas when you need a cleaner or more advanced version.