VLOOKUP question

[Anthony]

Hi,

Cell B2 contains the formula =TODAY() formatted as mmmm
Cells S20:S31 contain the month's of the year
Cells T20:T31 contain specific data

in cell E8 I want a formula that will seach cells S20:S31 for the month
contained in cell B2 and return the value in the corresponding cell in cells
T20:T31

I have tried =VLOOKUP("B2",$S$20:$X$31,2)

which I want to search for February and return the value in the coresponding
cell, but I keep getting the #VALUE error

anybody any ideas ??

PS take it easy I aint that good at this stuff !!

Cheers

[Creator]

Hi Anthony, try the following:

=VLOOKUP(MONTH(B2),$S$20:$X$31,2)

Cells S20 to S21 should contain values 1, 2, 3, ... representing the month
values jan, feb, mar, ...

Hope it works.
--
Creator


"Anthony" wrote:

> Hi,
>
> Cell B2 contains the formula =TODAY() formatted as mmmm
> Cells S20:S31 contain the month's of the year
> Cells T20:T31 contain specific data
>
> in cell E8 I want a formula that will seach cells S20:S31 for the month
> contained in cell B2 and return the value in the corresponding cell in cells
> T20:T31
>
> I have tried =VLOOKUP("B2",$S$20:$X$31,2)
>
> which I want to search for February and return the value in the coresponding
> cell, but I keep getting the #VALUE error
>
> anybody any ideas ??
>
> PS take it easy I aint that good at this stuff !!
>
> Cheers
>

[Anthony]

Creator,
Thanks for help.....but.....

I get the #N/A error !

In cells S20:S31 are the tweleve months, and the data in T20:T31 is not in
order, does this cause a problem, as I have read that you mat need to add,
FALSE on the end of the formula.

any other ideas ??

Thanks again

"Creator" wrote:

> Hi Anthony, try the following:
>
> =VLOOKUP(MONTH(B2),$S$20:$X$31,2)
>
> Cells S20 to S21 should contain values 1, 2, 3, ... representing the month
> values jan, feb, mar, ...
>
> Hope it works.
> --
> Creator
>
>
> "Anthony" wrote:
>
> > Hi,
> >
> > Cell B2 contains the formula =TODAY() formatted as mmmm
> > Cells S20:S31 contain the month's of the year
> > Cells T20:T31 contain specific data
> >
> > in cell E8 I want a formula that will seach cells S20:S31 for the month
> > contained in cell B2 and return the value in the corresponding cell in cells
> > T20:T31
> >
> > I have tried =VLOOKUP("B2",$S$20:$X$31,2)
> >
> > which I want to search for February and return the value in the coresponding
> > cell, but I keep getting the #VALUE error
> >
> > anybody any ideas ??
> >
> > PS take it easy I aint that good at this stuff !!
> >
> > Cheers
> >

[Creator]

Anthony,

1. initially you got the error #VALUE because you enclosed B2 in quotes like
"B2"; so don't do this.

2. The values in column S need to be in ASCENDING order; the order of the
items in the other columns do not matter.

3. Make sure you have numerical values in column S. Text data may lead to
the error #N/A like you reported.


--
Creator


"Anthony" wrote:

> Creator,
> Thanks for help.....but.....
>
> I get the #N/A error !
>
> In cells S20:S31 are the tweleve months, and the data in T20:T31 is not in
> order, does this cause a problem, as I have read that you mat need to add,
> FALSE on the end of the formula.
>
> any other ideas ??
>
> Thanks again
>
> "Creator" wrote:
>
> > Hi Anthony, try the following:
> >
> > =VLOOKUP(MONTH(B2),$S$20:$X$31,2)
> >
> > Cells S20 to S21 should contain values 1, 2, 3, ... representing the month
> > values jan, feb, mar, ...
> >
> > Hope it works.
> > --
> > Creator
> >
> >
> > "Anthony" wrote:
> >
> > > Hi,
> > >
> > > Cell B2 contains the formula =TODAY() formatted as mmmm
> > > Cells S20:S31 contain the month's of the year
> > > Cells T20:T31 contain specific data
> > >
> > > in cell E8 I want a formula that will seach cells S20:S31 for the month
> > > contained in cell B2 and return the value in the corresponding cell in cells
> > > T20:T31
> > >
> > > I have tried =VLOOKUP("B2",$S$20:$X$31,2)
> > >
> > > which I want to search for February and return the value in the coresponding
> > > cell, but I keep getting the #VALUE error
> > >
> > > anybody any ideas ??
> > >
> > > PS take it easy I aint that good at this stuff !!
> > >
> > > Cheers
> > >

[daddylonglegs]

If S20:S31 contains the months of the year, i.e. in S20 the text "January" up to "December" in S31 then you could use this formula

=VLOOKUP(TEXT(B2,"mmmm"),$S$20:$T$31,2,0)

[Anthony]

Guys
thanks for your replies, I am now at home, so I will try out your
suggestions when I return to work tomorrow and maybe post back then. So
thanks for now

"Creator" wrote:

> Anthony,
>
> 1. initially you got the error #VALUE because you enclosed B2 in quotes like
> "B2"; so don't do this.
>
> 2. The values in column S need to be in ASCENDING order; the order of the
> items in the other columns do not matter.
>
> 3. Make sure you have numerical values in column S. Text data may lead to
> the error #N/A like you reported.
>
>
> --
> Creator
>
>
> "Anthony" wrote:
>
> > Creator,
> > Thanks for help.....but.....
> >
> > I get the #N/A error !
> >
> > In cells S20:S31 are the tweleve months, and the data in T20:T31 is not in
> > order, does this cause a problem, as I have read that you mat need to add,
> > FALSE on the end of the formula.
> >
> > any other ideas ??
> >
> > Thanks again
> >
> > "Creator" wrote:
> >
> > > Hi Anthony, try the following:
> > >
> > > =VLOOKUP(MONTH(B2),$S$20:$X$31,2)
> > >
> > > Cells S20 to S21 should contain values 1, 2, 3, ... representing the month
> > > values jan, feb, mar, ...
> > >
> > > Hope it works.
> > > --
> > > Creator
> > >
> > >
> > > "Anthony" wrote:
> > >
> > > > Hi,
> > > >
> > > > Cell B2 contains the formula =TODAY() formatted as mmmm
> > > > Cells S20:S31 contain the month's of the year
> > > > Cells T20:T31 contain specific data
> > > >
> > > > in cell E8 I want a formula that will seach cells S20:S31 for the month
> > > > contained in cell B2 and return the value in the corresponding cell in cells
> > > > T20:T31
> > > >
> > > > I have tried =VLOOKUP("B2",$S$20:$X$31,2)
> > > >
> > > > which I want to search for February and return the value in the coresponding
> > > > cell, but I keep getting the #VALUE error
> > > >
> > > > anybody any ideas ??
> > > >
> > > > PS take it easy I aint that good at this stuff !!
> > > >
> > > > Cheers
> > > >

[qwopzxnm]

Try this:

One reason this isn't working is becaust the today() formula returns a number that corresponds to todays current date. When you try to vlook that up against Months, it will not match.

If you don't want your months to be numbers such as 1, 2, 3, etc. and this spreadsheet data will eventually have more than 1 years data I would suggest the following.

Make cell B2 =Date(Year(today()),Month(today()),1)

Then make sure your S20 - S31 cells are actualy dates, and not typed in text. For example January would be 1/1/06, February would be 2/1/06, etc.

Both cell B2 and your range of Months can be formatted to read as "January 2006"

Now your vlookup should work (Without the B2 in quotes as previously stated)

If this is trying to look up previous years data then you could also do this:
Make cell B2 =Date(Year(today())-1,Month(today()),1)