Index not Reurn the correct Result
 

I have the following Index formula which is not producing the correct result
and I am unsure of why

{=INDEX(StartTime_HoursWorked,MATCH(1,(Date_HoursW orked=H$9)*(StaffNo_HoursWorked=$B$13)))}

My named ranges are using the Offset formula, all worked off
"StaffNo_HoursWorked" who's formula is

=OFFSET(Database1!$A$2,0,0,COUNTA(Database1!$A:$A) ,1)

My guess is the above Offset formula is the problem. As I have dynamic
values in y Database, this is why I am using this

Any help would be appreciated

Thanks

Index not Reurn the correct Result
 

John wrote...
I have the following Index formula which is not producing the correct result
and I am unsure of why

{=INDEX(StartTime_HoursWorked,MATCH(1,
(Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$13 )))}
....

The problem is that you're looking for the first exact match, so you
need to include the 3rd argument to MATCH, and that argument needs to
be zero or FALSE.

=INDEX(StartTime_HoursWorked,MATCH(1,
(Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$13) ,0))

Index not Reurn the correct Result
 

Thanks Harlan for your guidance, problem now is it returns a #N/A value.

In my test data I only have 4 rows, with each named range being the same
size, so not sure why. The value returned in B13 does appear in the range
"StaffNo_HoursWorked" as does the value in H9 in Date_HoursWorked


"Harlan Grove" wrote in message
oups.com...
John wrote...
I have the following Index formula which is not producing the correct
result
and I am unsure of why

{=INDEX(StartTime_HoursWorked,MATCH(1,
(Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$1 3)))}
...

The problem is that you're looking for the first exact match, so you
need to include the 3rd argument to MATCH, and that argument needs to
be zero or FALSE.

=INDEX(StartTime_HoursWorked,MATCH(1,
(Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$13) ,0))

Index not Reurn the correct Result
 

John wrote...
Thanks Harlan for your guidance, problem now is it returns a #N/A value.
....

What does the formula

=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursW orked=$B$13))

return?

Index not Reurn the correct Result
 

0 (Zero), or I had it formatted in Time format 12:00am


"Harlan Grove" wrote in message
oups.com...
John wrote...
Thanks Harlan for your guidance, problem now is it returns a #N/A value.
...

What does the formula

=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursW orked=$B$13))

return?

Index not Reurn the correct Result
 

Harlan

This #N/A maybe caused by the way I produce the value in H(, which has the
following

="("&TEXT((Home!$K$12-5),"dd/mm/yy"&")")

Maybe Im just trying to be too clever. When I just type a date in H9 it
returns a value of 1 in the formula


"John" wrote in message
...
0 (Zero), or I had it formatted in Time format 12:00am


"Harlan Grove" wrote in message
oups.com...
John wrote...
Thanks Harlan for your guidance, problem now is it returns a #N/A value.
...

What does the formula

=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursW orked=$B$13))

return?

Index not Reurn the correct Result
 

John wrote...
This #N/A maybe caused by the way I produce the value in H(, which has the
following

="("&TEXT((Home!$K$12-5),"dd/mm/yy"&")")

Maybe Im just trying to be too clever. When I just type a date in H9 it
returns a value of 1 in the formula

"John" wrote in message
0 (Zero), or I had it formatted in Time format 12:00am

"Harlan Grove" wrote in message
....
What does the formula

=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_Hou rsWorked=$B$13))

return?

You're getting #N/A errors because of your H9 value and maybe also your
B13 value. It seems Date_HoursWorked is a range containing date values.
If so, it'd never equal the text value given by the formula you show
above. In Excel, numbers never equal text. Replace the H9 formula with

=Home!$K$12-5

and format it as you see fit.

Index not Reurn the correct Result
 

Thanks Harlan, I created a "white cell" in H8 with Home!$K$12-5 etc and left
H9 visible and worked the formula off of H8


"Harlan Grove" wrote in message
oups.com...
John wrote...
This #N/A maybe caused by the way I produce the value in H(, which has the
following

="("&TEXT((Home!$K$12-5),"dd/mm/yy"&")")

Maybe Im just trying to be too clever. When I just type a date in H9 it
returns a value of 1 in the formula

"John" wrote in message
0 (Zero), or I had it formatted in Time format 12:00am

"Harlan Grove" wrote in message
...
What does the formula

=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_Ho ursWorked=$B$13))

return?

You're getting #N/A errors because of your H9 value and maybe also your
B13 value. It seems Date_HoursWorked is a range containing date values.
If so, it'd never equal the text value given by the formula you show
above. In Excel, numbers never equal text. Replace the H9 formula with

=Home!$K$12-5

and format it as you see fit.