Can I reference different named ranges in a lookup function?

helpneeded2

Board Regular
Joined
Jun 25, 2021
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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:

ABC
12016data 1data 2
22017data 1data 2
32018data 1data 2
42019data 1data 2
52020data 1data 2
6Lifetimedata 1data 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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
What is customerNumber?
 
Upvote 0
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".
 
Upvote 0
There is nothing wrong with the formula you posted, in what way isn't it working for you?
 
Upvote 0
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.
 
Upvote 0
If you have a named range called table_2016 & it has at least 2 columns then both of these should work
Excel Formula:
=vlookup(queryYear,table_2016,2,0)
=vlookup(queryYear,indirect("table_2016"),2,0)
 
Upvote 0
If you have a named range called table_2016 & it has at least 2 columns then both of these should work
Excel Formula:
=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:

Code:
=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.
 
Upvote 0
Unfortunately I can think of no reason why that would happen.
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top