display value based on current month

**XLS-EXCEL** · 10-02-2007, 11:11 AM

Hi Everyone

I have a table with the months of year and certain numbers in each of those columns.

E.g.

Jan...........Feb...........March
41............34.5..........89

etc.

I have another cell in which I want to display 41 if the current month is January or display 34.5 if the current month is Feb and so on.

Any ideas?

**daddylonglegs** · 10-02-2007, 11:17 AM

One way....

Assuming Months are all 3 letter abbreviations in A1:L1 and values below in A2:L2

=HLOOKUP(TEXT(TODAY(),"mmm"),A1:L2,2,0)

**XLS-EXCEL** · 10-02-2007, 12:20 PM

thanks DLL

Whats the 2, and 0 at the end?

Also The way I had to do it was by making a new row of months for each row of data (example in the attached sheet).
I was having a problem getting the formula to refer to certain cells in certain columns and rows from a table.

Any way around it?

Many Thanks!!

Regards

**NBVC** · 10-02-2007, 12:57 PM

If you look at excel Help for Hlookup... you'll see that the 2 is the row index number (this is the row number within your defined range that the data is to come from). The 0 is the same as FALSE...it tells the Hlookup formula to pull exact matches only.

So there is no need to add those date rows... First expand the table range in the formula to include all rows (e.g. B11:P14 after deleting the extra rows) and then just simply change the 2 to a 3 to get the 3rd row info, to a 4 to get the 4th row and so on.

If you want to create the formula once and copy down....

try =HLOOKUP(TEXT(TODAY(),"mmm"),'Data Table'!$B$10:$P$14,Row($A2),0) copied down.

**XLS-EXCEL** · 10-03-2007, 11:34 AM

thanks NBVC

However I tried the suggested formula u showed but in the second column it shows the actual name of the month rather than the value of the cell belonging to that month.

If you try this with the spreadsheet I attached with my previous post using the originaly table to refer to (at the top of the sheet) rather than the alternative one I made then you will see what I mean.

Appreciate your time and effort of reading all this and helping me!

Regards

**NBVC** · 10-03-2007, 12:03 PM

The table at the top is not the same as your revised one....

The table at top has merged column headers and each month has 2 columns of information....so Hlookup won't really work so neatly...

You could use Index/Match combo...and assuming you want the data from the "Required" subcolumns...

Try in F20:

=INDEX($B$2:$AF$7,ROW($A3),MATCH(TEXT(TODAY(),"mmm"),'Data Table'!$B$2:$AF$2,0)+1)

copied down...

Note this will take info from the first column(s) corresponding to the the month of today()...

**XLS-EXCEL** · 10-04-2007, 06:48 AM

Yea thats right, I want to use the table at the top rather than the table at the bottom. And yes I just want it to pick out the numbers in the "Required " Column from each month.

I pasted ur formula in F20 but says !N/A. Dunno why. I guess there is something I am not doing right.

Thanks..

**NBVC** · 10-04-2007, 07:53 AM

Hi XLS-EXCEL,

I don't know why this forum has this annoying quirk of adding spaces in formulas that should not be there....

It looks like a space was added after the "mmm" in the TEXT() function....there shouldn't be one there.

It should be:

Please Login or Register  to view this content.

Note: putting formula in CODE wraps seems to correct the phantom space problem.

P.S. For consistency sake (and to avoid future errors coming up) you should keep all month names to 3 chars....i.e. "Sept" should be "Sep". I fixed that too in the attached....