Home › Excel formulas › Lookup and Reference functions
Excel Lookup and Reference Functions: Full List with Examples
Lookup and reference functions find a value in a table or point to another cell. VLOOKUP, XLOOKUP, INDEX and MATCH sit here and are the most asked functions in Excel interviews. Open any function for its syntax, arguments, a worked example and a free practice spreadsheet.
| Function | What it does |
|---|---|
| VLOOKUP | Looks up a value in the first column of a range and returns a value from another column, same row. |
| HLOOKUP | Like VLOOKUP but searches across the first row and pulls a value down a column. |
| XLOOKUP | The modern replacement for VLOOKUP — searches any column and returns from any other, no column counting needed. |
| INDEX | Returns the value at a given row/column position within a range. |
| MATCH | Finds the position of a value within a range — usually paired with INDEX. |
| CHOOSE | Picks one value from a list based on a position number. |
| INDIRECT | Turns a text string into an actual cell reference. |
| OFFSET | Returns a range that's a certain number of rows/columns away from a starting cell. |
| ADDRESS | Builds a cell address as text from a row and column number. |
| ROW | Returns the row number of a reference. |
| COLUMN | Returns the column number of a reference. |
| ROWS | Counts how many rows are in a range. |
| COLUMNS | Counts how many columns are in a range. |
| TRANSPOSE | Flips a range from rows to columns or vice versa. |
| LOOKUP | A simpler, older lookup formula — requires sorted data. |
| FILTER | Returns only the rows of a range that match a condition. |
| SORT | Sorts a range automatically as a formula, without changing the original data. |
| UNIQUE | Returns a list of distinct values from a range. |
| HYPERLINK | Creates a clickable shortcut that jumps to another location. |
| GETPIVOTDATA | Pulls a specific value out of a PivotTable. |
| AREAS | Returns the number of separate areas in a reference. |
| FORMULATEXT | Returns a formula as text, so you can see it displayed in another cell. |
| XMATCH | The modern replacement for MATCH — more flexible matching and search options. |
| TAKE | Returns a set number of rows or columns from the start or end of an array. |
| DROP | Excludes a set number of rows or columns from an array. |
| EXPAND | Expands an array to a specified size, padding with a chosen value. |
| HSTACK | Combines arrays side by side into one array. |
| VSTACK | Combines arrays on top of each other into one array. |
| TOCOL | Returns an array as a single column. |
| TOROW | Returns an array as a single row. |
| CHOOSEROWS | Returns specific rows from an array by their position. |
| CHOOSECOLS | Returns specific columns from an array by their position. |
| RTD | Retrieves real-time data from a program that supports COM automation. |
| INDEX + MATCH | Combines INDEX and MATCH to look up a value — more flexible than VLOOKUP since it can look left and doesn't break if columns are inserted. |
| VLOOKUP + IFERROR | Wraps VLOOKUP in IFERROR so that a failed lookup shows a clean message instead of a #N/A error. |
| INDEX + MATCH (2-way lookup) | Uses two MATCH functions inside one INDEX to look up a value at the intersection of a specific row AND column — a 2D lookup. |
| INDEX + MATCH + IFERROR | Wraps an INDEX+MATCH lookup in IFERROR so a missing value shows a clean message instead of #N/A. |
| VLOOKUP + MATCH (dynamic column) | Uses MATCH to find which column number to pull from, so VLOOKUP still works correctly even if columns get reordered. |
| XLOOKUP + IFNA | Wraps XLOOKUP in IFNA to show a clean fallback message when the lookup value isn't found. |
| INDEX + MATCH (multiple criteria) | Uses an array-style MATCH to find a row that satisfies two conditions at once, then returns a value from that row. |
| HLOOKUP + MATCH (dynamic row) | Uses MATCH to find which row number to pull from a horizontally laid-out table, instead of hardcoding it. |
| INDEX + MATCH + MAX (find the top item) | Finds the highest number in a range, then uses MATCH+INDEX to pull back the LABEL associated with it. |
| INDEX + MATCH + MIN (find the bottom item) | Finds the lowest number in a range, then uses MATCH+INDEX to pull back the LABEL associated with it. |
| INDEX + SMALL + IF (nth match, array formula) | An advanced array formula that returns the Nth matching result when multiple rows meet the same condition (VLOOKUP only ever returns the first). |
| VLOOKUP + CHOOSE (reversed lookup) | Uses CHOOSE to rearrange columns on the fly, letting VLOOKUP look up a value using a column that's positioned AFTER the result column (which plain VLOOKUP can't do). |
| INDEX + MATCH with wildcards | Uses wildcard characters inside MATCH to find a partial text match instead of requiring an exact match. |
| VLOOKUP + ROW (fill down variant) | Uses ROW() to automatically shift which row of results a dragged-down VLOOKUP pulls from. |
| INDEX + OFFSET + MATCH (dynamic lookup range) | Combines OFFSET (to build a range that grows with the data) with INDEX+MATCH so the lookup keeps working as rows are added. |
| INDIRECT + MATCH (dynamic sheet reference) | Uses INDIRECT to build a reference to a different sheet based on text, then searches it with MATCH. |
Practice Lookup and Reference functions free
Type formulas in a real spreadsheet and get instant feedback.
Start practice → Formulas cheat sheetFrequently asked questions
What are the main Excel Lookup and Reference functions?
Cellgrids lists 49 Excel Lookup and Reference functions. Popular ones include VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH. Each has its own page with syntax, arguments and an example.
How can I practice Excel Lookup and Reference functions online?
Open the free live spreadsheet on Cellgrids, type the function in a cell and check your answer. No Excel install or sign-up is needed to start.
Which Excel Lookup and Reference function should a beginner learn first?
Start with VLOOKUP. Looks up a value in the first column of a range and returns a value from another column, same row.