Skip to main content
Login
Cart
Quick, clean, and to the point
Training
Videos
Functions
Formulas
Shortcuts
Blog
Search
Excel INDIRECT Function
Summary
The Excel INDIRECT function returns a valid reference from a given
text string. Use INDIRECT when you need to convert a reference
assembled as text into a proper reference.
Purpose
Create a reference from text
Return value
A valid worksheet reference.
Syntax
=INDIRECT (ref_text, [a1])
Arguments
ref_text - A reference supplied as text.
a1 - [optional] A boolean to indicate A1 or R1C1-style
reference. Default is TRUE = A1 style.
Usage notes
Use INDIRECT to create or supply a reference in text form. Indirect is
useful when you want to convert a text value into a valid cell
reference.
The reference created by INDIRECT will not change even when cells,
rows, or columns are inserted or deleted. For example, the formula
=INDIRECT("A1:A100") will always refer to the first 100 rows of
column A, even if rows in that range are deleted or inserted.
References created by INDIRECT are evaluated in real time and
the content of the reference is displayed.
When ref_text is an external reference to another workbook,
the workbook must be open.
a1 is optional. When omitted, a1 is TRUE = A1 style reference.
When a1 is set to FALSE, INDIRECT will created a an R1C1-style
reference.
INDIRECT formula examples
Lookup with variable sheet name
To create a lookup with a variable sheet name, you can use the
VLOOKUP function together with the INDIRECT function. In the
example shown, the formula in C5 is:
=VLOOKUP($B5,INDIRECT("'"&C$4&...
3D SUMIF for multiple worksheets
If you need to conditionally sum identical ranges that exist in
separate worksheets, all in one formula, you can do so with the
SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the
example, the formula looks like...
Formula with locked reference
to create a formula with a "locked" reference a reference that
won't be adjusted during copy or paste, or when rows and columns
are changed in a worksheet you can use the INDIRECT function.
How this formula works...
Worksheet name exists
To test if a worksheet name exists in a workbook, you can use a
formula based on the ISREF and INDIRECT functions. In the example
shown, the formula in C5 is: =ISREF(INDIRECT(B5&"!A1")) How this
formula...
Dynamic workbook reference
To build a dynamic worksheet reference - a reference to another
workbook that is created with a formula based on variables that may
change - you can use a formula based on the INDIRECT function. In
the example shown,...
Sum top n values
To sum the top values in a range, you can use a formula based on
the LARGE function, wrapped inside the SUMPRODUCT function. In
the generic form of the formula (above), rng represents a range of
cells that contain...
Convert column letter to number
To convert a column letter to an regular number (e.g. 1, 10, 26, etc.)
you can use a formula based on the INDIRECT and COLUMN
functions. In the example shown, the formula in C5 is:
=COLUMN(INDIRECT(B5&"1...
Dynamic lookup table with INDIRECT
To allow a dynamic lookup table, you can use the INDIRECT function
with named ranges inside of VLOOKUP. In the example shown the
formula in G5 is: =VLOOKUP(F5,INDIRECT(E5),2,0) Background The
purpose of this...
Create array of numbers
To create an array of numbers like {1;2;3;4;5} you can use
a formula based on the ROW and INDIRECT functions. This technique
is most often used in array formulas that need a numeric array for
processing of some kind....
Count day of week between dates
To count the number of Mondays, Fridays, Sundays, etc. between
two dates you can use an array formula that uses several functions:
SUMPRODUCT, WEEKDAY, ROW, and INDIRECT. In the example
shown, the formula in cell E6 is...
Solid Excel Formula Training
Learn Excel formulas and functions with clear, concise videos.
Master absolute/relative addresses, dates, text, named ranges, and
tools for troubleshooting. Each video comes with a practice
worksheet and audio transcript. Instant access and complete 100%
guarantee. Start today!
See details.
500 Formula Examples, thoughtfully explained.
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy
PDF.
Get the PDF
Topic Guide
Function Guide
Formula Examples
Excel Shortcuts
Pivot Tables
Excel Charts
Conditional formatting
Nested IF examples
How-to videos
Video training
Excel people
Excel books
Recent comments
Key functions
IF function
VLOOKUP function
SUMIFS function
COUNTIFS function
INDEX function
MATCH function
SUMPRODUCT function
Hi - I'm Dave Bruns, and I run Exceljet with my wife,
Lisa. Our goal is to help you work faster in Excel. We create short
videos, and clear examples of formulas, functions, pivot tables,
conditional formatting, and charts. Read more.
Thank you for creating this website.
Outstanding! - Stefan
Excel video training
Quick, clean, and to the point.
Learn more
2012-2017 Exceljet.
Home
About
Blog
Contact
Feedback
Twitter
Facebook
Google+
RSS
16Shares
