# Thread: wildcard in named range in formula Thanks:  1 Post #5084275 (1) Likes: 0

1. ## wildcard in named range in formula

Doing calculations for my fantasy NFL teams.
I have a sheet with multiple named ranges, FPweek1, FPweek2, etc .
Can I ref that range in a formula, (vlookup in my case) where the number changes based on a number at the top of the column . ex: vlookup(playername,FPweek(a1),4,false) - where A1 has the value 1
so when I copy this formula to the next column the FPweek# changes.

2. ## Re: wildcard in named range in formula

Originally Posted by freakofnature
Doing calculations for my fantasy NFL teams.
I have a sheet with multiple named ranges, FPweek1, FPweek2, etc .
Can I ref that range in a formula, (vlookup in my case) where the number changes based on a number at the top of the column . ex: vlookup(playername,FPweek(a1),4,false) - where A1 has the value 1
so when I copy this formula to the next column the FPweek# changes.
You should be able to use the INDIRECT function. See if something like this works...

VLOOKUP(playername,INDIRECT("FPweek"&A1),4,FALSE)

3. ## Re: wildcard in named range in formula

Try

=VLOOKUP(playername,INDIRECT("FPweek"&A1),4,0)

4. ## Re: wildcard in named range in formula

However, the indirect method won't work if your named ranges are dynamic (using functions like counta to define row numbers).
In that case, you need to do something like

=VLOOKUP(playername,CHOOSE(A1,FPweek1,FPweek2,FPweek3,etc.),2,0)

5. ## Re: wildcard in named range in formula

Originally Posted by Jonmo1
However, the indirect method won't work if your named ranges are dynamic (using functions like counta to define row numbers).
It seems to work when I try it... can you give me an example?

6. ## Re: wildcard in named range in formula

In my case the ranges are set at this point, FPweek1 won't really change.

7. ## Re: wildcard in named range in formula

& thx I'll try INDIRECT, I was looking at it but have no experience with it so wasn't sure.

8. ## Re: wildcard in named range in formula

Originally Posted by Rick Rothstein
can you give me an example?
ABCDEFG
11a1
2b2
3b#REF!2c3

Sheet1

Worksheet Formulas
CellFormula
B3=VLOOKUP(A3,INDIRECT("FPweek"&A1),2,0)
C3=VLOOKUP(A3,FPweek1,2,0)

Workbook Defined Names
NameRefers To
FPweek1=OFFSET(Sheet1!\$F\$1,0,0,COUNTA(Sheet1!\$F:\$F),2)

9. ## Re: wildcard in named range in formula

Originally Posted by Jonmo1
Workbook Defined Names
Name Refers To
FPweek1 =OFFSET(Sheet1!\$F\$1,0,0,COUNTA(Sheet1!\$F:\$F),2)
Sorry, my fault... I read what you wrote incorrectly. You did say the defined name was dynamic and for some reason, I misread that as referring to the formula in the cell that was producing the number. Thanks for following up.

10. ## Re: wildcard in named range in formula

Originally Posted by freakofnature
& thx I'll try INDIRECT
You're welcome.