Nested If

[Ess]

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?

 

[Elkar]

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

 

[Dave Peterson]

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]

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.

 

[Ess]

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.

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.

 

[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).

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?