jeudi 2 avril 2015

excel: search column for text value, if value exists check other column for number of days from the date today?

i have a excel spreadsheet like so:



A B
27/03/2015 Riddor
28/03/2015 Text
09/03/2015 Bees


What i am trying to do is search my column B for the word "Riddor" using this function:



=IF(ISERROR(MATCH("Riddor",Statistics!B:B,0)),"No Match",TODAY()-MIN(Statistics!B:B))


Within this function if the value 'riddor' is not found i want to show 'no match' else if the value is found i want to identify the most recent occurrence of the value riddor in my column where its date in column A is the most recent from today. I then want to count the number of days.


So for instance my value riddor in column B has a date of 27/03/2015


the number of days from this date to today is 5 days, however if i add another riddor with a more recent date like so:



27/03/2015
31/03/2015


then i want my day count to show 1 day instead of the previous 5 days.


Can someone please show me where i am going wrong and what to do to get this working, thanks in advance.


Aucun commentaire:

Enregistrer un commentaire