The INDIRECT function in Excel helps the users to lock the specified cell in a formula. Therefore, without changing the formula itself, we can modify cell references within a formula. Sometimes while working with multiple databases we need to perform dynamic VLOOKUP in those databases for values. We can do this easily with the combination of the INDIRECT and the VLOOKUP function. In this article, we will learn how to perform the INDIRECT VLOOKUP formula.
Read more: Combine SUMIF and VLOOKUP in Excel
Let’s take a quick view of our today’s task.
Download Practice Workbook
Download this practice sheet to practice the task while you are reading this article.
INDIRECT Function in Excel: Definition
The INDIRECT function returns a cell reference specified by a given text string. This function helps to lock the cell in a formula. Therefore, the indirect reference will not change even if we insert new columns or rows into the worksheet.
Returns the reference specified by a text string.
- Ref_text is the reference supplied as text. It generally contains A1-style, R1C1-style reference (Required).
- [a1] is the logical value that specifies the type of reference contained in the cell ref_text (optional).
INDIRECT with VLOOKUP
The INDIRECT VLOOKUP formula is a remarkably powerful formula when we use this formula for multiple worksheets. Here is a scenario. Consider you have an assignment where you are given some mobile phone name and their model data from 2017-2021. Now you have to assemble those names and their model systematically in a new worksheet. This can easily be done by the INDIRECT VLOOKUP formula. Let’s learn!
In the following example, we have some “Mobile Name” and “Model” from the year 2017-2021 in different worksheets. For the “2017” and “2018” worksheets,
In the “2019” and “2020” worksheets,
And finally the data for the “2021” worksheet,
Create another table in a new worksheet. This table contains the “Mobile Name” column and the associated year “2017”, “2018”, “2019”, “2020”, “2021” columns. We need to retrieve the model from these years for the given “Mobile Name”.
Now we will apply the “INDIRECT VLOOKUP” formula. The generic formula is,
Now insert the values into the formula and the final formula is
- Lookup_value is $B4
- Table_array is created using this function INDIRECT(“‘”&C$3&”‘!”&”B4:C8”). The mixed reference C$3 refers to the column heading (2017) which is matched with the worksheet names. The “Concatenation Operator (&)” is used to join the single quote character(“&C$3&”) to either side. To create a specific worksheet reference, the “Exclamation Point (!)” is joined on the right side of the formula. The output of this concatenation is a “Text” which then the “INDIRECT” function uses as reference.
- Column_index_number is “2”.
- We want the EXACT match (FALSE).
Now apply this INDIRECT VLOOKUP formula by pressing “Enter”. We have got the model name “iPhone X” for the year “2017”.
Step-4: Select the cell that contains the formula, hold and drag the cursor to apply the same formula for the rest of the cells. Our final result is here.
Things to Remember
⏩For the “INDIRECT” function, if ref_text is not a valid cell reference, the function will return the #REF! error value.
⏩The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function “Never” searches for the data on the left.
⏩When you select your “Lookup_value” you have to use the absolute cell references ($) to block the array.
⏩If you enter a value less than “1” as the column index number, the function will return an error.
The powerful combo “INDIRECT VLOOKUP” is discussed using an example in this article. We hope this article proves useful to you. If you have any thoughts regarding this article please do share in our comment section.
- VLOOKUP Example Between Two Sheets in Excel
- How to Pull Data from Multiple Worksheets in Excel (4 Quick Ways)
- VLOOKUP from Another Sheet in Excel
- VLOOKUP and Return All Matches in Excel (7 Ways)
- VLOOKUP with Numbers in Excel (4 Examples)
- Use of VLOOKUP in VBA to Find Values from Another Worksheet in Excel
- 10 Best Practices with VLOOKUP in Excel