Multi function look up/if/match

Hi All,

I have attached a very abridged version of the spreadsheet I am working on, hopefully it works as I have never attached any thing here before.

Background. I have been undertaking motion sensing camera monitoring of wildlife for the last 5 years. I have over 200,000 records of species. I have split this down into each species, some of which have well over 10,000 records alone. An example of the data captured is in the Fox Raw Tab. This includes data about when the camera was put out, when it came in, when the record was taken, what species it was, and the site name.

I am working with an ecologist who wants to undertake an 'occupancy' analysis of this data. This involves providing data about when species are seen in the form of zeros and ones against the date, zero is no record that day and one is any number of records that day.

The format they also want the data in is day 1 being the start of the entire program (1/7/2011) and day 2192 as the end (30/6/2017).

The issue I face is that the each site (there are over 400) may have only had a camera out for a month, some two months, some the whole time. The ecologist only wants a zero or one in the cells that correspond to the time the camera was at that site and active, any other time should return NA. Without this their analysis will not be at all accurate.

In the attached file there are various tabs, what the tabs are. Fax Raw is the raw data as briefly described above. Fox Admin is where I have done a countifs which gives me the count of records against the date and time +12 hour column in fox raw (the ecologist wants days to be from midday to midday, not mid night to midnight, hence the plus 12 hours). This works and I can get to zeros and ones easily, what I can't do is get the NA to come up. The Fox Occ tab has the data in zeros and ones. and Sites Admin is irrelevant to this but needed to maintain the raw site data correctly.

What I need to be able to do. Enter a formula into foxOcc C4 (or a whole new tab set up the same way), that will search for a match of the site code in FoxOcc A and the site code in FoxRaw column L, and then sort to see if the date in FoxOcc row2 is between the dates in FoxRaw Columns C&E. If it isn't between these dates then return a NA, and if it is between these dates then return the corresponding cell value from Fox Admin as a one or zero (fox admin is a count so has higher numbers than 1.

I hope all this makes sense and I didn't provide too much background, I included it thinking the context might help.

Thanks in advance.

Chris