vlookup, indexing, oh my....

[JEB]

Totally lost on this one.

I have a date values in Col I that are fed in from another sheet. These
dates may be typed in by a user in any format, but the critical info the year
and the month.

I have a series of months and years listed in Col A with corresponding info
in Col B that is only pertinent to that info in Col B. (Col B is the average
monthly outdoor temperature).

In another Column (let's call it M), I want to "index/lookup" the month and
year in Col I, match it with the month and year in Col A, and return the
value in Col B.

Any ideas?

 

[Luke M]

How exactly is your data entered in I and A? If you're using numbers (or
numbers with some type of date), this is fairly easy. If you're using true
text like "Jan 2010". Another assumption is that column A is sorted.
Possible ideas:
=LOOKUP(I2,A:B)
=LOOKUP(TEXT(I2,"mm-yyyy"),A:B)
=SUMPRODUCT(--(TEXT(I2,"mmyyyy")=TEXT(A1:A100,"mmyyyy")),B1:B100)

 

[JEB]

Data is entered in I numbers, i think.

Data in A1 is first entered however the user types it in, then A2 is
=DATE(YEAR(D8),MONTH(D8)+1,DAY(D8)) and so forth for each cell below.

but I used =LOOKUP(I2,A:B) and it worked just fine!

Thanks!

 

[JEB]

Ok, here's a similar scenario, but slightly different.

This time, I have a date values in Col I that are fed in from another sheet.
These
dates may be typed in by a user in any format, but the critical info this
time is just the month.

I have a series of months listed in Col C with corresponding info
in Col B that is only pertinent to that info in Col D. (Col D is the
historical
monthly outdoor temperature).

This time, in another Column (let's call it N), I want to "index/lookup" the
month Col I, match it with the month in Col C, and return the value in Col D.

Values in Column C can be listed in any format to simply get it to work. It
is just data.

 

[JEB]

Nevermind, I got it to work using the
=SUMPRODUCT(--(TEXT(I2,"mm")=TEXT(A1:A100,"mm")),B1:B100
example!

Thanks!