MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Use Indirect To Get A Data From A Multi-cell Range


February 15, 2021 - by Bill Jelen

Challenge: As described in several other topics, INDIRECT is pretty cool for grabbing a value from a cell. Can INDIRECT point to a multi-cell range and be used in a VLOOKUP or SUMIF function?

Solution: You can build an INDIRECT function that points to a range. The range might be used as the lookup table in a VLOOKUP or as a range in SUMIF or COUNTIF.


In Figure 36, the formula pulls data from the worksheets specified in row 4. The second argument in the SUMIF function looks for records that match a certain date from column A.

Note: Because each worksheet might have a different number of records, I chose to have each range extend to 300. This is a number that is sufficiently larger than the number of transactions on any sheet. The formula in cell B5 is:

=SUMIF(INDIRECT(B$4&"!A2:A300"), $A5, INDIRECT(B$4&"!C2:C300"))

Figure 36. Each INDIRECT points to a rectangular range on the other worksheet.
Figure 36. Each INDIRECT points to a rectangular range on the other worksheet.

Summary: You can use INDIRECT to grab data from a multi-cell range.

Title Photo: Boba Jaglicic at Unsplash.com


This article is an excerpt from Excel Gurus Gone Wild.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.