Index not Reurn the correct Result

J

John

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

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
 
H

Harlan Grove

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)))}
Click to expand...
....

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

John

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
 
H

Harlan Grove

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

What does the formula

=SUMPRODUCT((Date_HoursWorked=H$9)*(StaffNo_HoursWorked=$B$13))

return?
 
J

John

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
 
H

Harlan Grove

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
Click to expand...

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.
 
J

John

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
 
Top