CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
satancorpse
satancorpse

Reputation: 331

Get the name of "named data range" from a cell in Google Sheets

I have a range of data that I want to use vlookup on. I named the range as "june_salaries". The goal is to generate payslips for each employee based on the data range (june_salaries).

My query is that is there a way to instead of writing something like 

=VLOOKUP($B$6,june_salaries,4,0)

I can simply refer to a single cell for the "named range"? For example, let's say I put the text "june_salaries" in cell A1, and when I use vlookup, for the range of data I will simply refer to that cell.

The reason why I want to do that this way is the salary sheet changes each month and so is the name of the range. But if I can refer the data range in a single cell, I can simply change the name of the month from June to July or anything.

Hope the query is clear. Please let me know if further information would be required.

Upvotes: 1

Views: 1017

Answers (2)

player0
player0

Reputation: 1

as mentioned, you can use INDIRECT to reference a cell value/string and reflect it into a formula

=VLOOKUP(B6, INDIRECT(F1), 4, 0)

array formula (if needed) of that would be:

=ARRAYFORMULA(IFERROR(VLOOKUP(B6:B, INDIRECT(F1), 4, 0)))

HOWEVER, you could automate it (if you plan to edit F1 cell once per month for each month)

=VLOOKUP(B6, 
 INDIRECT(LOWER(TEXT(DATE(2000, MONTH(TODAY())-1, 1), "mmmm"))&"_salaries"), 4, 0)

eg. this month it will return you june_salaries, next month it will return you july_salaries, etc.

Upvotes: 1

pnuts
pnuts

Reputation: 59475

Assuming june_salaries in F1. Wrap your named range name in INDIRECT:

=VLOOKUP($B$6,indirect(F1),4,0)

Upvotes: 0

Related Questions