Can I reference different named ranges in a lookup function?

[helpneeded2]

I have a table, that consists of a data related to several years. Each year has its own sheet of data. The sheet for each year, is just the name of the sheet. I have assigned a named ranged to the data in sheet and referenced is "table_YEAR", where-in the 2016 worksheet, the named range is "table_2016".

Now, in my main worksheet, I have a column listing the years from 2016 to 2020, and a row at the bottom labeled "Lifetime", which links to data in a worksheet called "Lifetime".

What I would like to do, is include in my VLOOKUP the ability to reference the appropriate sheet based on the value in the year column.

Example table:

	A	B	C
1	2016	data 1	data 2
2	2017	data 1	data 2
3	2018	data 1	data 2
4	2019	data 1	data 2
5	2020	data 1	data 2
6	Lifetime	data 1	data 2

Ideally I want to structure the lookup as follows -- this would be the formula in B2 and would be copied down and across unto C and all other following columns: =vlookup(customerNumber,INDIRECT("table_"&A1),2,0)

However when I try to do this I get an error, which I believe is due to me likely using indirect incorrectly?

Could someone please advise if it is possible to include an indirect reference in a lookup, when I am trying to combine a cell value with text.

Thank you.

 

[Fluff]

What is customerNumber?

 

[helpneeded2]

What is customerNumber?

I was trying to simplify the example from my actual table, and I should have renamed that. It is a just a named range for a specific cell that a user enters the year number which they want the lookup to query. I can't edit the above post, but if I could I would rename that something like "queryYear".

 

[Fluff]

There is nothing wrong with the formula you posted, in what way isn't it working for you?

 

[helpneeded2]

There is nothing wrong with the formula you posted, in what way isn't it working for you?

When I evaluate the formula, I can see that the Indirect is getting a #REF error. If I just replace the Indirect with: =vlookup(queryYear,table_2016,2,0) then it works fine, but it just doesn't like the Indirect being used for some reason. I am certainly not very experienced with the Indirect statement, but I also tested it with =vlookup(customerNumber,INDIRECT("table_2016"),2,0) as to my understanding that should just output table2016 without quotes, but instead it shows a #REF error when I do this as well.

 

[Fluff]

If you have a named range called table_2016 & it has at least 2 columns then both of these should work

=vlookup(queryYear,table_2016,2,0)
=vlookup(queryYear,indirect("table_2016"),2,0)

 

[helpneeded2]

If you have a named range called table_2016 & it has at least 2 columns then both of these should work

=vlookup(queryYear,table_2016,2,0)
=vlookup(queryYear,indirect("table_2016"),2,0)

That was my understanding too, but the second one is not working.

Here are my actual lines of code I am experimenting with:

=VLOOKUP(value_patronId,table_2016,6,0)
=VLOOKUP(value_patronId,INDIRECT("table_2016"),6,0)

It works fine with the first line. The second line gives the reference error.

 

[Fluff]

Unfortunately I can think of no reason why that would happen.

 

[helpneeded2]

Unfortunately I can think of no reason why that would happen.

Here is the same issue in this link. I don't have the technical background to understand or resolve the matter, but here is where someone else details some solutions:

Indirect and Vlookup Reference Errors

Ive constructed the following path with the code below: 'I:\Capital Markets\Post Closing\Audit\Team Reports\Audit Time\2017\February\[feb12017.xlsm]TimeInOffice'!A:C Using this code =("'I:\Capi...

stackoverflow.com