Here, we will learn how to use the Excel lookup and reference function: INDIRECT. Later, we will also learn how to find range total using INDIRECT and the VLOOKUP and SUM functions.
INDIRECT function
The Excel INDIRECT function returns a valid cell reference from the given text string. For example, suppose the value 3 is in A2 (see the image), and the text string "A2" in B2. Enter the following formula in C2: =INDIRECT(B2). The function gets the text string "A2", goes to A2 and returns the value of it, which is 3.
INDIRECT takes two arguments: ref_text, [a1]. The first required argument ref_text will take a cell reference in the form of a text string or a named range. The second optional argument a1 controls the type of reference contained in the ref_text argument. It is a Boolean argument that accepts TRUE or FALSE. Specifying TRUE will interpret ref_text as an A1-style cell reference. Specifying FALSE will interpret ref_text as an R1C1 reference. Omitting the argument will take the default value of TRUE.
Finding range total using INDIRECT with VLOOKUP and SUM
To find the range total based on the selection from the drop-down list, use data validation and a formula based on the VLOOKUP, INDIRECT and SUM functions.
The dataset (shown in the image) contains the data about fee collections of three months: January, February and March. The goal is to find the fee total based on the selection from the drop-down list.
To accomplish the task, create drop-down list using data validation in C13, including fee abbreviations in the range A6:A10. Name the following ranges in the Name Box: Tf - C6:E6, Rf - C7:E7, Ef - C8:E8, Tpf - C9:E9 and Bf - C10:E10.
The formula in C15 is:
=SUM(INDIRECT(VLOOKUP(C13,A6:E11,1,FALSE)))
How this formula works
Suppose "Rf" is selected from the drop-down list.
VLOOKUP looks up the text "Rf" in C13 in the range A6:E11 and returns the matching value "Rf" from the first column of the range A6:E11. The VLOOKUP result goes to INDIRECT in the form of the named range "Rf" that returns the values of it in the spill range. These values goes to SUM that sums up the values as the final output.
You have successfully learnt how to use the Excel lookup and reference function: INDIRECT, and how to find range total using INDIRECT and the VLOOKUP and SUM functions! I hope this post helped you.
