Using range names in formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to use a cell reference within the vlookup function, but cannot get it to work
The cell I want to reference is a concatenate of "M"+(cell reference 1)+"y"+(cell reference 2) where cell reference 1 is number 7 and cell reference 2 is number 1981 (actually month and year of birth). Cell references 1 and 2 are user entry cells and are therefore subject to change
I have created a range name of m7y1981
I can create a function of VLOOKUP(C32,M7Y1981,B3) which works, but I want the m7y1981 to change as the month and date mentioned above change, ie,m8y1946 - this being a different range
Can anyone help?
 
Mulgrew6

One way:

VLOOKUP(C32,INDIRECT("M"&Cell1&"Y"&Cell2),B3)


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Mulgrew6 said:
I want to use a cell reference within the vlookup function, but cannot get it to work.
The cell I want to reference is a concatenate of "M"+(cell reference
Click to expand...
1)+"y"+(cell reference 2) where cell reference 1 is number 7 and cell
reference 2 is number 1981 (actually month and year of birth). Cell
references 1 and 2 are user entry cells and are therefore subject to change.
I have created a range name of m7y1981.
I can create a function of VLOOKUP(C32,M7Y1981,B3) which works, but I want
Click to expand...
the m7y1981 to change as the month and date mentioned above change,
ie,m8y1946 - this being a different range.
 
I tried this =VLOOKUP(A3,INDIRECT("M"&A2&"x"),2,FALSE)
and it worked. You will need to modify it a bit to suit your problem

Best Wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


Mulgrew6 said:
I want to use a cell reference within the vlookup function, but cannot get it to work.
The cell I want to reference is a concatenate of "M"+(cell reference
Click to expand...
1)+"y"+(cell reference 2) where cell reference 1 is number 7 and cell
reference 2 is number 1981 (actually month and year of birth). Cell
references 1 and 2 are user entry cells and are therefore subject to change.
I have created a range name of m7y1981.
I can create a function of VLOOKUP(C32,M7Y1981,B3) which works, but I want
Click to expand...
the m7y1981 to change as the month and date mentioned above change,
ie,m8y1946 - this being a different range.
 
Provided your named ranges are in the same workbook, you
can use INDIRECT to construct a text string which refers
to a range name or address. If your entry cells are A1 &
B1, then the following function should do the trick:

=Vlookup(c32,indirect("M" & A1 & "Y" & B1),b3,false)

Cheers, Pete
The cell I want to reference is a concatenate of "M"+
Click to expand...
(cell reference 1)+"y"+(cell reference 2) where cell
reference 1 is number 7 and cell reference 2 is number
1981 (actually month and year of birth). Cell references 1
and 2 are user entry cells and are therefore subject to
change.
I can create a function of VLOOKUP(C32,M7Y1981,B3) which
Click to expand...
works, but I want the m7y1981 to change as the month and
date mentioned above change, ie,m8y1946 - this being a
different range.
 
Back
Top