Index & Match???

[jonnywakey]

Hi All

On the attached spreadsheet I am trying to find the latest location (Column B) of a job (Column A) based on a unique value (column D) when compared to current date / time (G1)
whilst comparing to Date / time in (column C).

In other words the values in column D are a unique filter macro of Column A values then I want (E1) to look at (D1) then look down (A1:A1000) and return last location from (B) compared to time entered in (C) when compared to NOW() in G1

I've used the Index and Match function in an array but it seems to fail on me for some reason.

Is there a better way to do this?

Thanks

Jonny

[Pete_UK]

Your date/times in column C all look the same, and in fact are linking to some other workbook. They seem to be text values, rather than proper date/times, so it will be difficult to compare them with G1, or to find the latest date/time for any particular job.

Pete

[jonnywakey]

Apologies Pete, thanks for your post.

Ive removed the links and altered the times, I had this working before with an index match but for some reason it wouldn't accept data being copied and pasted into the spreadsheet (the data comes from a query in MS Access but is exported in .xls format)

Hope you can help, much appreciated if anyone can, original formula here which was in cells E1:E100

=INDEX($B$1:$B$25,MATCH(MAX(IF(A1:A25=D2,IF($C$1:$C$25<=$K$1,$C$1:$C$25))),$C$1:$C$25,0),1)

Thanks

Jonny

[Pete_UK]

I've just amended your formula slightly (shown in red):

=INDEX($B$1:$B$25,MATCH(MAX(IF(A$1:A$25=D2,IF($C$1:$C$25<=$G$1,$C$1:$C$25))),$C$1:$C$25,0),1)

Note that this is an array formula, so must be committed using Ctrl-Shift-Enter (CSE) rather than the usual <Enter>.

I think you must have changed the cell where you have the =NOW() formula.

Hope this helps.

Pete