vlookup, indexing, oh my....
 

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?

vlookup, indexing, oh my....
 

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 )

--
Best Regards,

Luke M
"JEB" wrote in message
...
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?

vlookup, indexing, oh my....
 

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!


"Luke M" wrote:

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 )

--
Best Regards,

Luke M
"JEB" wrote in message
...
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.

vlookup, indexing, oh my....
 

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.
"Luke M" wrote:

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 )

vlookup, indexing, oh my....
 

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

Thanks!

"JEB" wrote:

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.
"Luke M" wrote:

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 )