Excel formula library

Excel Tutorial

Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.

Start with first formula Browse formulas

127 Formulas

11 Categories

3 Skill levels

Live Search & filters

Formula list

Find any Excel formula

Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.

Chapter 4

Lookup & Reference

34 VLOOKUP =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Searches vertically down the first column of a table for a specific value and retrieves corresponding data from another column in the same row. Lookup & Reference Intermediate Classic
Syntax
```
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
```
Arguments

lookup_value

The value to search for in the first column of the table.

table_array

Two or more columns of data in which data is searched.

col_index_num

The column number in table_array from which the matching value must be returned (1-based).

[range_lookup]

A logical value: FALSE or 0 for EXACT match; TRUE or 1 for APPROXIMATE match.
Sample
```
=VLOOKUP("Kabir Verma", A2:D5, 2, 0)
```
Result IT
Sample data
Download

A B C D
2 Rohan Sharma HR 350 Delhi
3 Kabir Verma IT 400 Mumbai
4 Meena Gupta Sales 450 Pune

Pro tip
Always pass 0 or FALSE as the 4th argument unless intentionally working with sorted tax or commission tier brackets.

Common pitfall
VLOOKUP CANNOT look to the left! The lookup column MUST be the very first column in table_array. Use INDEX/MATCH or XLOOKUP for left lookups.
35 HLOOKUP =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Searches horizontally across the top row of a table for a key and returns a value from a specified row beneath that matching column. Lookup & Reference Intermediate Classic
Syntax
```
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
```
Arguments

lookup_value

The value to find in the first row of the table.

table_array

A table of information in which data is looked up.

row_index_num

The row number in table_array from which matching value will be returned.

[range_lookup]

FALSE or 0 for exact match, TRUE or 1 for approximate match.
Sample
```
=HLOOKUP("Q2", A1:D3, 2, 0)
```
Result 48000
Sample data
Download

Pro tip
Use TRANSPOSE on horizontal data if you prefer working with standard modern VLOOKUP or XLOOKUP.

Common pitfall
The search row must strictly be the top row of the selected table_array range.
36 INDEX =INDEX(array, row_num, [column_num]) Returns a value or reference of the cell at the intersection of a particular row and column in a given range. Lightning-fast and flexible. Lookup & Reference Advanced Classic
Syntax
```
=INDEX(array, row_num, [column_num])
```
Arguments

array

A range of cells or an array constant.

row_num

The row in the array from which to return a value.

[column_num]

The column in the array from which to return a value.
Sample
```
=INDEX(A2:D5, 2, 2)
```
Result IT
Sample data
Download

A B C D
2 E-101 HR 350 Delhi
3 E-102 IT 400 Mumbai
4 E-103 Sales 450 Pune

Pro tip
INDEX does not slow down large workbooks like VLOOKUP because it only references specific columns rather than whole table arrays.

Common pitfall
Passing row_num or column_num larger than the dimensions of the array triggers a #REF! error.
37 MATCH =MATCH(lookup_value, lookup_array, [match_type]) Searches for a value in an array and returns its relative position. Often combined with INDEX to create an unbreakable lookup system. Lookup & Reference Advanced Classic
Syntax
```
=MATCH(lookup_value, lookup_array, [match_type])
```
Arguments

lookup_value

The value that you want to match in lookup_array.

lookup_array

The single row or column of cells being searched.

[match_type]

1 (less than), 0 (exact match), or -1 (greater than). Default is 1.
Sample
```
=MATCH("Kabir Verma", A2:A5, 0)
```
Result 2
Sample data
Download

A B C D
2 Rohan Sharma HR 350 Position 1
3 Kabir Verma IT 400 Position 2 (Match!)
4 Meena Gupta Sales 450 Position 3

Pro tip
Pair INDEX and MATCH: =INDEX(B2:B5, MATCH("Kabir Verma", A2:A5, 0)) for unbreakable lookups immune to column insertions.

Common pitfall
Lookup_array must be a 1-dimensional range (either 1 column or 1 row), never a 2D block.
38 OFFSET =OFFSET(reference, rows, cols, [height], [width]) Returns a reference to a range offset from a starting cell by a designated number of rows and columns, optionally with custom height and width. Lookup & Reference Advanced Classic
Syntax
```
=OFFSET(reference, rows, cols, [height], [width])
```
Arguments

reference

Starting anchor cell reference.

rows

Number of rows to move down (positive) or up (negative).

cols

Number of columns to move right (positive) or left (negative).

[height], [width]

Optional dimensions of returned range.
Sample
```
=OFFSET(A1, 2, 1)
```
Result IT
Sample data
Download

Pro tip
OFFSET is volatile and recalculates on every workbook change; prefer INDEX with colons for non-volatile dynamic ranges.

Common pitfall
Offsetting beyond the boundaries of the worksheet (e.g. moving left from column A) causes a #REF! error.
39 CHOOSE =CHOOSE(index_num, value1, [value2], ...) Uses index_num to return a value from a list of up to 254 values. Acts as a simple programmatic switch statement. Lookup & Reference Intermediate Classic
Syntax
```
=CHOOSE(index_num, value1, [value2], ...)
```
Arguments

index_num

Specifies which value argument is selected (1 to 254).

value1, [value2, ...]

1 to 254 values, references, formulas, or range names.
Sample
```
=CHOOSE(2, "Rohan", "Kabir", "Meena")
```
Result Kabir
Sample data
Download

A B C D
2 2 1:Rohan, 2:Kabir, 3:Meena Kabir Picked 2nd value
3 1 1:Rohan, 2:Kabir, 3:Meena Rohan Picked 1st value
4 3 1:Rohan, 2:Kabir, 3:Meena Meena Picked 3rd value

Pro tip
Can dynamically choose entire ranges: =SUM(CHOOSE(A1, Q1_Sales, Q2_Sales, Q3_Sales)).

Common pitfall
If index_num is less than 1 or greater than the number of items provided, CHOOSE returns #VALUE!.
40 LOOKUP =LOOKUP(lookup_value, lookup_vector, [result_vector]) Looks up a value in a sorted one-row or one-column range and returns a value from the same position in a second range. Lookup & Reference Intermediate Classic
Syntax
```
=LOOKUP(lookup_value, lookup_vector, [result_vector])
```
Arguments

lookup_value

A value that LOOKUP searches for in the lookup_vector.

lookup_vector

A range that contains only one row or one column, sorted ascending.

[result_vector]

A range that contains only one row or column, same size as lookup_vector.
Sample
```
=LOOKUP(102, A2:A5, B2:B5)
```
Result Meena Gupta
Sample data
Download

A B C D
2 100 Rohan Sharma HR Target match
3 101 Kabir Verma IT Target match
4 102 Meena Gupta Sales Meena Gupta

Pro tip
The classic formula =LOOKUP(2, 1/(A2:A100""), A2:A100) reliably finds the very last non-empty value in column A.

Common pitfall
Values in lookup_vector must be sorted in ascending order; otherwise LOOKUP returns incorrect values.
41 ROW =ROW([reference]) Returns the row number of a cell reference. If reference is omitted, it assumes the reference is the cell in which ROW appears. Lookup & Reference Beginner Classic
Syntax
```
=ROW([reference])
```
Arguments

[reference]

Optional cell or range of cells whose row number you want.
Sample
```
=ROW()
```
Result 2
Sample data
Download

A B C D
2 Keyboard 2 1 (=ROW()-1) Located in Row 2
3 Mouse 3 2 (=ROW()-1) Located in Row 3
4 Monitor 4 3 (=ROW()-1) Located in Row 4

Pro tip
To create bulletproof auto-renumbering that survives row deletions: =ROW() - ROW($A$1).

Common pitfall
If reference is a range of cells, ROW returns only the row number of the top-left cell unless entered as an array formula.
42 ROWS =ROWS(array) Returns the number of rows in a reference or array. Excellent for determining table lengths and loop bounds. Lookup & Reference Beginner Classic
Syntax
```
=ROWS(array)
```
Arguments

array

An array, an array formula, or a reference to a range of cells.
Sample
```
=ROWS(A2:D10)
```
Result 9
Sample data
Download

A B C D
2 A2:D10 Row 2 Row 10 9 rows total
3 B1:B100 Row 1 Row 100 100 rows
4 A5:F5 Row 5 Row 5 1 row

Pro tip
Unlike ROW() which gives position, ROWS() gives dimension (count).

Common pitfall
Passing an invalid range reference causes a #REF! error.
43 COLUMN =COLUMN([reference]) Returns the column number of the given cell reference. A=1, B=2, C=3, etc. Lookup & Reference Beginner Classic
Syntax
```
=COLUMN([reference])
```
Arguments

[reference]

Optional cell or range for which you want the column number.
Sample
```
=COLUMN(C1)
```
Result 3
Sample data
Download

A B C D
2 =COLUMN(A1) ? 1 =COLUMN(B1) ? 2 =COLUMN(C1) ? 3 =COLUMN(D1) ? 4
3 First Col Second Col Third Col Fourth Col
4 Letter: A Letter: B Letter: C Letter: D

Pro tip
Use =COLUMN(B1) inside VLOOKUP col_index to make your formula auto-increment as you drag right.

Common pitfall
COLUMN() refers to the column of the current cell, so moving the formula cell changes its output.
44 COLUMNS =COLUMNS(array) Returns the number of columns in an array or reference. Essential for dynamic financial projections. Lookup & Reference Beginner Classic
Syntax
```
=COLUMNS(array)
```
Arguments

array

An array, array formula, or cell range reference.
Sample
```
=COLUMNS(A1:E1)
```
Result 5
Sample data
Download

A B C D
2 A1:E1 Col A Col E 5 columns
3 B2:D10 Col B Col D 3 columns
4 A1:A50 Col A Col A 1 column

Pro tip
Useful in financial model templates where financial statements span a variable number of forecast years.

Common pitfall
Non-contiguous ranges like (A1:B2, D1:E2) cannot be passed to COLUMNS.
45 ADDRESS =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Creates a cell reference as text given specified row and column numbers. Supports absolute and relative notation. Lookup & Reference Intermediate Classic
Syntax
```
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
```
Arguments

row_num

Numeric value representing row number.

column_num

Numeric value representing column number.

[abs_num]

1: $A$1 (absolute), 2: A$1, 3: $A1, 4: A1 (relative).
Sample
```
=ADDRESS(1, 1)
```
Result $A$1
Sample data
Download

A B C D
2 1 1 $A$1 Top-left corner cell
3 5 2 $B$5 Row 5, Column B
4 10 4 $D$10 Row 10, Column D

Pro tip
Use abs_num = 4 to get a clean relative address like "A1" without dollar signs: =ADDRESS(1, 1, 4).

Common pitfall
ADDRESS returns a text string, not an active live reference. You must wrap it in INDIRECT to extract its value.
46 INDIRECT =INDIRECT(ref_text, [a1]) Returns the reference specified by a text string. Lets you dynamically construct cell references or tab names on the fly. Lookup & Reference Advanced Classic
Syntax
```
=INDIRECT(ref_text, [a1])
```
Arguments

ref_text

A reference to a cell that contains an A1-style reference as text.
Sample
```
=INDIRECT(B2)
```
Result E-101
Sample data
Download

A B C D
2 E-101 A2 E-101 Pulled from cell A2
3 IT B2 IT Pulled dynamically
4 350 C2 350 Pulled dynamically

Pro tip
Essential for dynamic multi-tab rollups where sheet names are listed in a summary column: =INDIRECT("'" & A2 & "'!C10").

Common pitfall
INDIRECT is volatile and causes severe lag on massive workbooks. Also, references to closed external workbooks return #REF!.

	A	B	C	D
2	Rohan Sharma	HR	350	Delhi
3	Kabir Verma	IT	400	Mumbai
4	Meena Gupta	Sales	450	Pune

	A	B	C	D
2	Rohan Sharma	HR	350	Position 1
3	Kabir Verma	IT	400	Position 2 (Match!)
4	Meena Gupta	Sales	450	Position 3

	A	B	C	D
2	2	1:Rohan, 2:Kabir, 3:Meena	Kabir	Picked 2nd value
3	1	1:Rohan, 2:Kabir, 3:Meena	Rohan	Picked 1st value
4	3	1:Rohan, 2:Kabir, 3:Meena	Meena	Picked 3rd value

	A	B	C	D
2	100	Rohan Sharma	HR	Target match
3	101	Kabir Verma	IT	Target match
4	102	Meena Gupta	Sales	Meena Gupta

	A	B	C	D
2	Keyboard	2	1 (=ROW()-1)	Located in Row 2
3	Mouse	3	2 (=ROW()-1)	Located in Row 3
4	Monitor	4	3 (=ROW()-1)	Located in Row 4

	A	B	C	D
2	A2:D10	Row 2	Row 10	9 rows total
3	B1:B100	Row 1	Row 100	100 rows
4	A5:F5	Row 5	Row 5	1 row

	A	B	C	D
2	=COLUMN(A1) ? 1	=COLUMN(B1) ? 2	=COLUMN(C1) ? 3	=COLUMN(D1) ? 4
3	First Col	Second Col	Third Col	Fourth Col
4	Letter: A	Letter: B	Letter: C	Letter: D

	A	B	C	D
2	A1:E1	Col A	Col E	5 columns
3	B2:D10	Col B	Col D	3 columns
4	A1:A50	Col A	Col A	1 column

	A	B	C	D
2	1	1	$A$1	Top-left corner cell
3	5	2	$B$5	Row 5, Column B
4	10	4	$D$10	Row 10, Column D

	A	B	C	D
2	E-101	A2	E-101	Pulled from cell A2
3	IT	B2	IT	Pulled dynamically
4	350	C2	350	Pulled dynamically