Excel Vlookup with cell reference

Question

I have a cell range that I named "cell_range" in Excel. I want to extract the fourth row and fifth column from this table. The formula,

=vlookup(4,cell_range,5)

gives me the value I am looking for.

However, I also have the text "cell_range" in cell A1. Instead of typing out "cell_range" in my formula, I want to reference "cell_range" indirectly by referencing cell A1. The formula

vlookup(4,A1,5)

is giving me a "#N/A" error.

How can I indirectly reference the table "cell_range" in my formula?

Answer 1

Use INDIRECT:

=VLOOKUP(4,INDIRECT(A1),5)

Answer 2

While you can use INDIRECT to perform this kind of stuff, I steer clear of it for reasons I've outlined at https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

In this particular case I'd use a lookup table and either CHOOSE or INDEX, as demonstrated in the screenshot below:

Note that you need to pre-specify your areas with my approach.

Here's the syntax for CHOOSE

=CHOOSE(index_num,value1,value2,...)

Here’s the translation from Microsoft-speak into Jeff-speak:

=CHOOSE(Which area do you want?, First area, Second area, ...)

If using a dropdown or a cell reference to provide the index_num argument, you simply use a lookup table that converts the output of the dropdown or cell input into an index number that tells CHOOSE which range from the list should be used.

In this example, you’re using this approach to choose which area on the spreadsheet to sum. But CHOOSE and INDEX can be used to do a lot more than that. For instance, you could use them to allow a user to dynamically pick which lookup table to use when doing a VLOOKUP. Or you could let the user dynamically pick which range in which sheet to user for some calculations do. Very powerful stuff indeed!

Answer 3

This is a great alternative to the use of INDIRECT. Indirect is intuitively easy to use but I agree that users should stay clear of it. For workbooks with many sheets I suggest using a macro to list them all rather than manually creating a list of sheets. See https://www.automateexcel.com/vba/list-all-sheets-in-workbook/