Forum Discussion

Tim Mullady's avatar
Tim Mullady
Copper Contributor
Jul 25, 2018

function help

I'm using the following formula and wanted to see if someone could help me tweak it a bit.

 

IFERROR(IF(VLOOKUP(C:C,SUMMIT!B:J,9,FALSE)+1<A2+1,"REGISTERED","NOT REGISTERED"),"NOT REGISTERED")

 

Column C has a code tied a person

 

Column A has a date of a transaction

 

The lookup is going to sheet with a registration date 9 columns out from the code.  The formula tells me if the person was registered on the date of the transaction or not.

 

The formula works properly but I just realized the data set has the same code listed multiple times with different registration dates.  People forgot their logins and system limitations list them again...

 

Can some one help me use the earliest Registration Date from the SUMMIT! data set.  9th column of the lookup range.  I'm assuming it needs some type of min() 

 

Any help would be greatly appreciated.

 

Thanks,

 

Tim

 

 

 

 

  • Hi Tim,

     

    VLOOKUP returns first found record, if your SUMMIT data is in chronological order that means the earliest registration.

    And your formula could be

    =IFERROR(IF(VLOOKUP(C2,SUMMIT!B:J,9,FALSE)<A2,"REGISTERED","NOT REGISTERED"),"NOT REGISTERED")

    Or that's not a case?

Resources