Range names: How can I use a formula to reference which range name to use?

ManCityPete

New Member
Joined
Oct 30, 2015
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with tabs for each month. On each worksheet I have created a named range, the name being based around the year and month. Each range has an entry for each day so 28-31 entries.

On a summary sheet, I want to use a vlookup formula to pull in the data from each month's sheet.

I can create the formula manually easily... =VLOOKUP(A1,+May_21,3,FALSE)

But this means I have to manually go down the list and create a separate formula for each month. So I can set B1 to produce May_21, but when I try VLOOKUP(A1,+B1,3,FALSE) it just returns an #VALUE! error.

It's easy for me to automatically generate the range name I want to look at, but how do I build that automatically into the VLOOKUP formula?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
You would need to use INDIRECT for that.
Excel Formula:
=VLOOKUP(A1,+INDIRECT(B1),3,FALSE)
I'm not seeing where the + comes into it. I've assumed that you are converting numbers formatted as text into proper numbers. In theory this should work the same as the original but I haven't tested it.
 
Upvote 0
You would need to use INDIRECT for that.
Excel Formula:
=VLOOKUP(A1,+INDIRECT(B1),3,FALSE)
I'm not seeing where the + comes into it. I've assumed that you are converting numbers formatted as text into proper numbers. In theory this should work the same as the original but I haven't tested it.
Perfect, thank you! The + was a hangover from me trying to find ways to do it; I saw it in other forumlae.

=VLOOKUP(A!,INDIRECT(B1),3,FALSE) works perfectly.

There's no logic here though is there?! I'd never even hjeard of INDIRECT before!

Cheers Jason
 
Upvote 0
INDIRECT has been around for a long time, quite possibly since the beginning of excel way back in 1985. It is effectively diverting the formula.

I suspect that there are many more functions that you have never heard of. Most people only know the functions that they use and rarely look at alternatives. I haven't checked the accuracy, but a quick search tells me that excel 2016 has 477 different functions. If that is correct then newer versions will be closer to 500, possibly more.

Looking at it from a different perspective with a simpler formula. =SUM(B1) is telling the formula to sum the value of B1, not to look at B1 and find a reference to a named range.
=SUM(INDIRECT(B1)) is telling the formula that B1 contains a reference to another range.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
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