VLOOKUP question

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[daddylonglegs]

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

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

 

[Guest]

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

 

[qwopzxnm]

Try this:

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)