How do I look up a value that falls within a range by hlookup function? I
want to lookup a date that matchs the same month in the target range and
return the value from the same row. For examples:

4/5/2009 to match May 2009 or
4/5/2009 to match 1/5/2009

Thanks!

Use MATCH to return the row and then use index to return the entry. Try this

A1: A12 is the month Jan 09, Feb 09, Mar 09....Dec 09
A15 = 4/5/2009

=MATCH(MONTH(A15),MONTH(A1:A12),0)

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"tywlam" wrote:

How do I look up a value that falls within a range by hlookup function? I
want to lookup a date that matchs the same month in the target range and
return the value from the same row. For examples:

4/5/2009 to match May 2009 or
4/5/2009 to match 1/5/2009

Thanks!

It doesn't work!! probably the format of lookup value and the format of
target range are not the same to compare! Anyway thanks.


"Jacob Skaria" wrote:

Use MATCH to return the row and then use index to return the entry. Try this

A1: A12 is the month Jan 09, Feb 09, Mar 09....Dec 09
A15 = 4/5/2009

=MATCH(MONTH(A15),MONTH(A1:A12),0)

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in the Formula Bar you can notice the curly braces at both ends
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"tywlam" wrote:

How do I look up a value that falls within a range by hlookup function? I
want to lookup a date that matchs the same month in the target range and
return the value from the same row. For examples:

4/5/2009 to match May 2009 or
4/5/2009 to match 1/5/2009

Thanks!

Not sure if this is what you want:
In D1:E2 I have this table
1 2 3 4 5 6 7 8 9 10 11 12
a b c d e f g h i j k l


In A1 I have a date (say 4-may-2009)
In B1 I have the formula =HLOOKUP(MONTH(A1),D1:O2,2,FALSE)
With any May date in A1, this formula returns the value "e"



If the table includes years:
In D1:E2 I have
200901 200902 200903 200904 200905 200906 200907 200908 200909 200910
200911 200912
a b c d e f g h i j k l


Now I use the formula: =HLOOKUP(YEAR(A1)*100+MONTH(A1),D1:O2,2,FALSE)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"tywlam" wrote in message
...
How do I look up a value that falls within a range by hlookup function? I
want to lookup a date that matchs the same month in the target range and
return the value from the same row. For examples:

4/5/2009 to match May 2009 or
4/5/2009 to match 1/5/2009

Thanks!

Thanks. Now go further. I have dates in row1 like: jan-2009, Feb-2009,....
where they indicate 1/1/2009, 1/2/2009,.....

I like to sum the range with dates in row1 that match a specified date in
cell b3. eg. sum value in a range with dates in May or earlier. I use sumif
and month function in a formula: =sumif(month(A1:L1),"<=month(b3)",
sum_Range) but it doesn't work and shows nothing!!

Pls help.


"Bernard Liengme" wrote:

Not sure if this is what you want:
In D1:E2 I have this table
1 2 3 4 5 6 7 8 9 10 11 12
a b c d e f g h i j k l


In A1 I have a date (say 4-may-2009)
In B1 I have the formula =HLOOKUP(MONTH(A1),D1:O2,2,FALSE)
With any May date in A1, this formula returns the value "e"



If the table includes years:
In D1:E2 I have
200901 200902 200903 200904 200905 200906 200907 200908 200909 200910
200911 200912
a b c d e f g h i j k l


Now I use the formula: =HLOOKUP(YEAR(A1)*100+MONTH(A1),D1:O2,2,FALSE)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"tywlam" wrote in message
...
How do I look up a value that falls within a range by hlookup function? I
want to lookup a date that matchs the same month in the target range and
return the value from the same row. For examples:

4/5/2009 to match May 2009 or
4/5/2009 to match 1/5/2009

Thanks!