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.

See if this is what you're looking for:

=IF(AND(B1=VLOOKUP(A1,'Sheet 1'!$A$1:$C$100,2,FALSE),B1<=VLOOKUP(A1,'Sheet
1'!$A$1:$C$100,3,FALSE)),VLOOKUP(A1,'Sheet 1'!$A$1:$C$100,2,FALSE),"")

Adjust the cell ranges to match your own.

HTH
Elkar



"Ess" wrote:

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.

Here is the criteria.

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

Assuming that the columns are in A:C on Sheet1 and A:B in sheet2 and your table
on sheet1 is in A1:C999:

This formula would go in C2 (headers in row 1) of Sheet2.

=index(sheet1!b1:b999,
match(1,(a2=sheet1!$a$1:$a$999)
*(b2=sheet1!$b$1:$b$999)
*(b2<=sheet1!$c1:$c999),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Ess wrote:

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.

--

Dave Peterson

Your logic does not include the Member ID match. I only want the admit date
if the Member ID matches AND the Provider Date is = Admit Date and <=
Discharge Date.
--
If you never attempt anything, you will never make any mistakes...thus never
enjoying the fruits of accomplishment.


"Elkar" wrote:

See if this is what you're looking for:

=IF(AND(B1=VLOOKUP(A1,'Sheet 1'!$A$1:$C$100,2,FALSE),B1<=VLOOKUP(A1,'Sheet
1'!$A$1:$C$100,3,FALSE)),VLOOKUP(A1,'Sheet 1'!$A$1:$C$100,2,FALSE),"")

Adjust the cell ranges to match your own.

HTH
Elkar



"Ess" wrote:

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.

I changed the formula to have the index on Member ID; however, my results are
giving me the MemberID from Sheet2, not the Admit Date. After reviewing the
logic, it's requesting a match on the Member ID. Should an IF statement be
included somewhere?

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


"Dave Peterson" wrote:

Here is the criteria.

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

Assuming that the columns are in A:C on Sheet1 and A:B in sheet2 and your table
on sheet1 is in A1:C999:

This formula would go in C2 (headers in row 1) of Sheet2.

=index(sheet1!b1:b999,
match(1,(a2=sheet1!$a$1:$a$999)
*(b2=sheet1!$b$1:$b$999)
*(b2<=sheet1!$c1:$c999),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Ess wrote:

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.

--

Dave Peterson

Nope. Just like there doesn't have to be an =if() portion for =vlookup().

You may want to share the formula you tried and explain what each field/column
is (again).

Ess wrote:

I changed the formula to have the index on Member ID; however, my results are
giving me the MemberID from Sheet2, not the Admit Date. After reviewing the
logic, it's requesting a match on the Member ID. Should an IF statement be
included somewhere?

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

"Dave Peterson" wrote:

Here is the criteria.

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

Assuming that the columns are in A:C on Sheet1 and A:B in sheet2 and your table
on sheet1 is in A1:C999:

This formula would go in C2 (headers in row 1) of Sheet2.

=index(sheet1!b1:b999,
match(1,(a2=sheet1!$a$1:$a$999)
*(b2=sheet1!$b$1:$b$999)
*(b2<=sheet1!$c1:$c999),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Ess wrote:

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.

--

Dave Peterson


--

Dave Peterson