Beyond VLOOKUP and INDEX MATCH, Excel has three other lookup-style functions that solve specific problems: LOOKUP for simple sorted-data lookups, INDIRECT for building cell references from text strings, and OFFSET for creating dynamic ranges. Here is how each one works and when to use it.
LOOKUP Function
LOOKUP searches a range for a value and returns a corresponding value from another range. It always performs an approximate match and requires the lookup data to be sorted in ascending order.Vector Form
The vector form searches a single row or column and returns a value from a corresponding row or column.
Syntax: =LOOKUP(lookupvalue, lookupvector, result_vector) Example:| A (Score) | B (Grade) |
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
=LOOKUP(85, A1:A5, B1:B5)
LOOKUP finds the largest value in A1:A5 that is less than or equal to 85. That is 80. It returns the corresponding value from B1:B5: "B".
Important: The lookup vector must be sorted in ascending order. LOOKUP does not have an exact-match mode — it always performs an approximate match (less-than-or-equal).Array Form
The array form takes a single table. If the table is taller than it is wide, LOOKUP searches the first column. If wider than tall, it searches the first row. It always returns from the last column or row.
Syntax: =LOOKUP(lookup_value, array)The array form is rarely used in modern Excel. Vector form is clearer and more predictable.
When to Use LOOKUP vs. VLOOKUP
Use LOOKUP when:
- Your data is sorted and you want an approximate match (grade scales, tax brackets)
- You want the simplest possible syntax for a threshold-based lookup
Use VLOOKUP when:
- You need exact-match lookups (FALSE or 0 as the fourth argument)
- Your data may not be sorted
Use INDEX MATCH for:
- Left lookups, large datasets, or when columns might be inserted
INDIRECT Function
INDIRECT converts a text string into a cell reference that Excel evaluates. Instead of pointing directly at a cell, you build the reference address as text and INDIRECT makes Excel treat it as a real reference. Syntax: =INDIRECT(ref_text, [a1])- ref_text — a text string representing a cell or range address
- a1 — TRUE (default) for A1-style references, FALSE for R1C1-style
Basic Example
=INDIRECT("A1")
This is equivalent to just =A1 — not useful on its own, but the power comes when the text string is dynamic.
Dynamic Sheet References
INDIRECT shines when you want to reference a cell on a different sheet, where the sheet name comes from another cell.
Suppose B1 contains the text "January" and you have a sheet named January. To pull cell A1 from that sheet:
=INDIRECT(B1 & "!A1")
Change B1 to "February" and the formula automatically pulls from the February sheet. This is impossible with a direct reference like =January!A1 — that is hardcoded.
Dynamic Named Range Reference
If you have named ranges called "North", "South", "East" and cell A1 contains one of those names:
=SUM(INDIRECT(A1))
This sums whichever named range matches the text in A1. Change A1 from "North" to "South" and the SUM updates.
When to Use INDIRECT
Use INDIRECT when:
- You need to build a reference from text (dynamic sheet names, variable column letters)
- You want a formula to point to a range whose address changes based on user input
Caution: INDIRECT is volatile — it recalculates every time any cell in the workbook changes, even if its inputs have not changed. In large workbooks, heavy use of INDIRECT can slow recalculation noticeably.
OFFSET Function
OFFSET returns a reference to a range that is a specified number of rows and columns away from a starting cell. It can return a single cell or an entire range of a specified size. Syntax: =OFFSET(reference, rows, cols, [height], [width])- reference — starting point
- rows — how many rows to move (positive = down, negative = up)
- cols — how many columns to move (positive = right, negative = left)
- height — optional, number of rows in the returned range
- width — optional, number of columns in the returned range
Basic Example
=OFFSET(A1, 2, 1)
Starts at A1, moves 2 rows down and 1 column right — returns the value in B3.
Returning a Range
=SUM(OFFSET(A1, 0, 0, 5, 1))
Returns a reference to a 5-row, 1-column range starting at A1 — equivalent to =SUM(A1:A5). This becomes useful when the size is dynamic.
Dynamic Range for a Chart or SUM
A common use: sum the last N rows of a column where N comes from a cell. If B1 contains the number of months to include:
=SUM(OFFSET(A10, 0, 0, -B1, 1))
Starts at A10, uses a negative height to go upward B1 rows. Change B1 from 3 to 6 and the SUM expands automatically.
OFFSET for Dynamic Drop-Down Lists
OFFSET combined with COUNTA creates a named range that expands as you add data:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
As a named range formula, this always covers exactly as many rows as there are entries in column A, making it useful for dynamic data validation lists.
Caution: Like INDIRECT, OFFSET is a volatile function and recalculates constantly. For static lookups, INDEX is preferred because it is non-volatile.Frequently Asked Questions
What is the main difference between LOOKUP and VLOOKUP?VLOOKUP requires the lookup column to be the leftmost column of the table array, supports exact match (fourth argument = FALSE/0), and lets you choose any return column by number. LOOKUP is simpler but always uses approximate match (no exact-match option) and requires sorted data. For most business use, VLOOKUP or INDEX MATCH is more appropriate than LOOKUP.
Can INDIRECT reference another workbook?Yes, but the other workbook must be open. The reference string must include the workbook name: =INDIRECT("[WorkbookName.xlsx]SheetName!A1"). If the referenced workbook is closed, INDIRECT returns a #REF! error. For cross-workbook references to closed files, use direct references or Power Query instead.
When would I use OFFSET instead of INDEX?Use OFFSET when you need to return a range of variable size (for charts, SUMIF ranges, or data validation lists that grow). INDEX can return a single cell by position but cannot return a variable-height range as a reference in the same way. Use INDEX for lookups; use OFFSET for dynamic ranges.
Why is my OFFSET formula showing a #REF! error?A #REF! error in OFFSET usually means the resulting reference is outside the worksheet boundaries. For example, =OFFSET(A1, -1, 0) tries to go one row above A1, which does not exist. Check that your rows and cols arguments do not push the reference off the sheet edges.