Need help with HLOOKUP and MATCH functions

[BEAR94]

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!

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Biff]

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:
Edit>Paste Special>Add>OK

Then with both rows 2 and 6 still selected Format>Cells>DATE>OK

Change your lookup formula to:

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

Biff

"BEAR94" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>