Look up Values

[Colleen45]

I am battling to structure this complex formula.
Again in a library scenario.
In Col S, it needs to check the number from Col R (card number), if it exists in Col A, if it does not exist, issue a message "Card not registered"
If it is in Col A, it needs to check if the card has expired or not from the expiry date in Col D, if it has expired issue a message "Card expired"
If the card is in Col A and it has not expired insert the card holders name from Col B into Col S
If there no card no. in Col R, Col S be "" as opposed to the standard message #N/A.
Your help would be greatly appreciated

 

[Scott Huish]

OK, I'm a little confused at the first line:

In Col S, it needs to check the number from Col R (card number), if it exists in Col A, if it does not exist, issue a message "Card not registered"

So, you want to say "Card not registered" whether it is in Column A or not?

 

[Colleen45]

I would like it to say "Card not Registered" if it is Not in Col A, if it is in Col A then proceed to the next part of the statement

 

[Scott Huish]

OK, that makes more sense.

Perhaps:

=IF(R1="","",IF(COUNTIF(A:A,R1),IF(VLOOKUP(R1,A:D,4,0)<TODAY(),"Card expired",VLOOKUP(R1,A:B,2,0)),"Card not registered"))

 

[Colleen45]

You are a Genius, thank you so much, it works EXACTLY as required
God Bless