INDIRECT VLOOKUP in Excel

INDIRECT VLOOKUP in Excel

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

                   Combining SUMPRODUCT and VLOOKUP in Excel

Quick View

Let’s take a quick view of our today’s task.

applying formula

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.

Summary

Returns the reference specified by a text string.

Syntax

=INDIRECT(ref_text, [a1])

Arguments

  • 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!

Read more: VLOOKUP Formula in Excel with Multiple Sheets

Step-1:

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,

creating table

creating table

In the “2019” and “2020” worksheets,

creating table

creating table

And finally the data for the “2021” worksheet,

creating table

Step-2:

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”.

creating table in a new worksheet

Step-3:

Now we will apply the “INDIRECT VLOOKUP” formula. The generic formula is,

=VLOOKUP(lookup_value, INDIRECT(“Table_Array”), col_index,0)

Now insert the values into the formula and the final formula is

=VLOOKUP($B4,INDIRECT(“‘”&C$3&”‘!”&”B4:C8”),2,FALSE)

Where,

  • 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).

applying formula

Now apply this INDIRECT VLOOKUP formula by pressing “Enter”. We have got the model name “iPhone X” for the year “2017”.

Getting result

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.

Final result

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.

Conclusion

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.


Further Readings:

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo