Can a vlookup be done on a 2 dimensional array?

[davegb]

I've never worked with VBA arrays before, and it's been many years since I
used them in a spreadsheet. Now I think they might help.

I have a spreadsheet with unusual dates. They're in MMM-YY format (no
days). I need to somehow convert them to month and year to compare with
some "normal" dates which are in mm/dd/yy format. Is it possible to create
a 2 dimensional array using Jan to Dec and 1 to 12 and then do a lookup to
get from the "mmm" in the spreadsheet to the month number to compare to
the "mm" in the date in the other column? Or do I have to put the months
and numbers into the spreadsheet and do a regular vlookup to convert from
text month to numeric month?

Thanks for your help.

 

[Niek Otten]

<They're in MMM-YY format>

Are they "real" Excel dates formatted asMMM-YY, or are they text strings?
If they are real Excel dates, just use Format>Cells>Number tab>Date and
choose an appropriate format.
If they are text strings, it depends on your date settings in Windows
Control Panel. Anyway, do try the DATEVALUE() function.
You can test whether it is a text string with the ISTEXT() function or the
other way around with the ISNUMBER() function (dates are numbers in Excel).
Last try indeed, use VLOOKUP tables. I´d be surprised if that proved to be
necessary, but who knows.........

 

[salgud]

<They're in MMM-YY format>

Are they "real" Excel dates formatted asMMM-YY, or are they text strings?
If they are real Excel dates, just use Format>Cells>Number tab>Date and
choose an appropriate format.
If they are text strings, it depends on your date settings in Windows
Control Panel. Anyway, do try the DATEVALUE() function.
You can test whether it is a text string with the ISTEXT() function or the
other way around with the ISNUMBER() function (dates are numbers in Excel).
Last try indeed, use VLOOKUP tables. I´d be surprised if that proved to be
necessary, but who knows.........

Thanks for your reply. I should have mentioned that the "MMM-YY" dates are
actually text, not XL dates. I tried the DATEVALUE function, but for some
reason, the cell showed =DATEVALUE(F3) instead of the XL datevalue. It does
show the numerical datevalue in the DATEVALUE function dialog box! Never
saw that before. I guess DATEVALUE won't work here.

You didn't reply to my question about using an array to do a lookup. Is
this a possible way to go?

 

[Rick Rothstein]

You don't need a look up function... you can get the month number using a
formula like this...

=TEXT(--("1-"&A1),"m")

where I have assumed the MMM-YY "date" is in A1.

 

[salgud]

=TEXT(--("1-"&A1),"m")

Thanks, Rick!