I need help using HLOOKUP and MATCH functions. I uses these 2 functions
several times before, but today I found confusing problem related to
them.

I attached a file to this e-mail that address the issue (my original
file is more complex).

In the attached file there is an array of end-of-month dates in row 2,
and numbers related to each date one row below.

I created another date array in row 6 that have same dates/one to two
days after dates in row 2.

I created a simple MATCH function in row 8 that if it works correctly
will output the column number, i.e. result in cell A8 should be 1, in
cell B8 should be 2, and so on.
However, the result that I got is totally different.


I also created HLOOKUP calculation in row 10. The function works well
if date in row 6 is exactly the same as in row 2. However, it does not
work for dates thta are slightly different, for example dates in column
E (12/29/2000 and 12/31/2000). I added an IF function in the formula
so if the dates are different the HLOOKUP function shall use value
TRUE, which will look for the largest value in the array, but smaller
than the lookup value. So for column E the right answer should be
12/29/2000, but the function spit out 06/30/2005.

Anybody knows what is wrong with my file? Does my result expectation
above correct? Anyone know how to fix this? Thanks!


+-------------------------------------------------------------------+
|Filename: PROBLEM.xls.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3686 |
+-------------------------------------------------------------------+

--
BEAR94
------------------------------------------------------------------------
BEAR94's Profile: http://www.excelforum.com/member.php...o&userid=26218
View this thread: http://www.excelforum.com/showthread...hreadid=395218

Hi!

Your dates are really TEXT entries and are not true Excel dates.

You can convert them to real dates by:

Select any empty cell and make sure that cell is formatted as GENERAL.
Copy that empty cell
Select all of rows 2 and 6
With the cursor inside one of those ranges, do:
EditPaste SpecialAddOK

Then with both rows 2 and 6 still selected FormatCellsDATEOK

Change your lookup formula to:

=HLOOKUP(A6,2:2,1,1)

Biff

"BEAR94" wrote in
message ...

I need help using HLOOKUP and MATCH functions. I uses these 2 functions
several times before, but today I found confusing problem related to
them.

I attached a file to this e-mail that address the issue (my original
file is more complex).

In the attached file there is an array of end-of-month dates in row 2,
and numbers related to each date one row below.

I created another date array in row 6 that have same dates/one to two
days after dates in row 2.

I created a simple MATCH function in row 8 that if it works correctly
will output the column number, i.e. result in cell A8 should be 1, in
cell B8 should be 2, and so on.
However, the result that I got is totally different.


I also created HLOOKUP calculation in row 10. The function works well
if date in row 6 is exactly the same as in row 2. However, it does not
work for dates thta are slightly different, for example dates in column
E (12/29/2000 and 12/31/2000). I added an IF function in the formula
so if the dates are different the HLOOKUP function shall use value
TRUE, which will look for the largest value in the array, but smaller
than the lookup value. So for column E the right answer should be
12/29/2000, but the function spit out 06/30/2005.

Anybody knows what is wrong with my file? Does my result expectation
above correct? Anyone know how to fix this? Thanks!


+-------------------------------------------------------------------+
|Filename: PROBLEM.xls.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3686 |
+-------------------------------------------------------------------+

--
BEAR94
------------------------------------------------------------------------
BEAR94's Profile:
http://www.excelforum.com/member.php...o&userid=26218
View this thread: http://www.excelforum.com/showthread...hreadid=395218