I am trying to create a nested if statement with information from two sheets.

Example:

Sheet 1: Member ID, Admit Date, Discharge Date
Sheet 2: Member ID, Provider Date

I want to pull the Admit date into Sheet 2.

Here is the criteria. If Sheet1.Member ID = Sheet2.Member ID And
Sheet2.Provider Date = Sheet1.Admit Date and <=Sheet1.Discharge Date, then
Sheet1.Admit Date on Sheet 2.

Is that possible?
--
If you never attempt anything, you will never make any mistakes...thus never
enjoying the fruits of accomplishment.