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))

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" <hrlngrv@aol.com> wrote in message
news:1145577651.580408.100790@g10g2000cwb.googlegroups.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$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))
>

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_HoursWorked=$B$13))

return?

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


"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1145596542.046978.197750@e56g2000cwe.googlegroups.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_HoursWorked=$B$13))
>
> return?
>

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" <r.@9999yahoo.co.uk> wrote in message
news:e29qbd$t8k$1@reader01.news.esat.net...
>0 (Zero), or I had it formatted in Time format 12:00am
>
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:1145596542.046978.197750@e56g2000cwe.googlegroups.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_HoursWorked=$B$13))
>>
>> return?
>>
>
>

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" <r.@9999yahoo.co.uk> wrote in message
>>0 (Zero), or I had it formatted in Time format 12:00am
>>
>>"Harlan Grove" <hrlngrv@aol.com> wrote in message
....
>>>What does the formula
>>>
>>>=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$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.

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" <hrlngrv@aol.com> wrote in message
news:1145642083.092860.108020@u72g2000cwu.googlegroups.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" <r.@9999yahoo.co.uk> wrote in message
>>>0 (Zero), or I had it formatted in Time format 12:00am
>>>
>>>"Harlan Grove" <hrlngrv@aol.com> wrote in message
> ...
>>>>What does the formula
>>>>
>>>>=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$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.
>