CODEWITHSUNDEEP
excelif-statementmatch
Avneesh Aggarwal
Avneesh Aggarwal

Reputation: 11

creating new column in excel based on conditions from two columns

this is what it looks like right now

this is what I want it to look like

What formulas can I use in excel to compute column D while simultaneously matching on column A,B and C.

If the case id is the same and date is the same, then if status is approved, final status is approved. but if case id is same date is same, and if status is denied, then final status is partial denial.

Can someone please help?

I tried do if and statement but I don't know how to match on unique case IDs in the same column.

Upvotes: 0

Views: 445

Answers (1)

brothergui
brothergui

Reputation: 11

It appears there is some missing information in your query. Let me write it out similar to an Excel IF statement:

=IF(CaseSame,IF(DateSame,IF(APPR,Appr,IF(DENY,PtDeny,??)),??),??)

With the information given, we don't know what to do in all outcomes. The outcomes marked with "??" will show as FALSE, because there is no output given for those criteria. But that formula would look like this:

=IF(A2=OFFSET(A2,-1,0),IF(B2=OFFSET(B2,-1,0),IF(C2="APPROVED","Approved",IF(C2="DENIED","Partial Denial",FALSE)),FALSE),FALSE)

And even there, I'm having to make the assumptions that you intend to go off the data in the row above for "case ID is the same" and "date is the same".

Are we supposed to assume that if no other criteria is given, to use column C's answer? In that case, there is still one unknown outcome in the IF statement:

=IF(C2="APPROVED","Approved",IF(C2="DENIED","Partial Denial",FALSE))

As long as no other entry is made in Column C, that should provide consistent results.

Here are what the two statements render in the Final Status column:

Results of both IF statements against your data and instructions

I hope this helps. With clearer information what to do in all circumstances, I may be able to provide a better answer.

Upvotes: 1

Related Questions