Evaluating a specified array for 2 (or more) criteria and returning a value

[CapRavOr]

So, I feel like this is a bit different from your typical INDEX/MATCH array function, but maybe not.

Essentially, the data shows 4 Procedure Categories:
- Office Visit
- Covid Test
- Covid Vaccine
- Not Specified

A patient can have Office Visit with no Covid Test, or an Office Visit with Covid Vaccines. The "Not Specified" category is negligible, but I'd still like to show it.

The request is to show patients with a combination of "Office Visit" and "Covid Test" by having the 2 occur in the same visit. Here is my sample data attached.

Ideally, I'd like to separate Office Visits Only, Covid Vaccines Only, and Office Visits with a Covid Test. Any help is appreciated!

[Limor_OP]

How about a PIVOTTABLE?

[CapRavOr]

How about a PIVOTTABLE?

That's essentially what I'm trying to create. I believe the client wants to see something more along the lines of "On 4/2, Patient A came to Clinic B and made an Office Visit. There was no Covid Test performed, so this was a routine Office Visit", "On 4/3, Patient B came to Clinic C and made an Office Visit with a Covid Test performed. This should be designated a 'Covid Test Visit'."

Sorry I wasn't more specific in my original post. This data will end up being a pivot table once the correct "Procedure Category" fields are populated as I described.

Thank you!

[kvsrinivasamurthy]

Pl see file.
3 tables are given for Office Visits Only, Covid Vaccines Only, and Office Visits with a Covid Test.
Each table has one formula
Formula 1 in L2

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(Table1,,1))/(COUNTIF($J$1:$K$1,INDEX(Table1,,6))>0),ROWS(L$2:L2))),"")

Formula 2 in T2

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(Table1,,1))/(COUNTIF($S$1,INDEX(Table1,,6))>0),ROWS(T$2:T2))),"")

Formula 3 in AB2

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(Table1,,1))/(COUNTIF($AA$1,INDEX(Table1,,6))>0),ROWS(AB$2:AB2))),"")

[CapRavOr]

Pl see file.
3 tables are given for Office Visits Only, Covid Vaccines Only, and Office Visits with a Covid Test.
Each table has one formula
Formula 1 in L2

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(Table1,,1))/(COUNTIF($J$1:$K$1,INDEX(Table1,,6))>0),ROWS(L$2:L2))),"")

Formula 2 in T2

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(Table1,,1))/(COUNTIF($S$1,INDEX(Table1,,6))>0),ROWS(T$2:T2))),"")

Formula 3 in AB2

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(INDEX(Table1,,1))/(COUNTIF($AA$1,INDEX(Table1,,6))>0),ROWS(AB$2:AB2))),"")

This is super close to what I need. Sorry for not being more specific in my original post.

From my original table, I would need a new column that looks at the table itself and evaluates for an array of "Same Patient Visits", and making the determinations, "Office Visit Only", "Covid Test Visit", and "Covid Vaccine".

I'm going to try to reframe the formulas you provided to fit that need, but would appreciate a solution in the meantime, if you arrive at one!

Thank you for the help!

[kvsrinivasamurthy]

Pl type expected results in the file and upload file.

[bebo021999]

Seem like to mark "X" if one ID has "Office Visit" and "Covid Test" in a same day?

G2:

=IF([@[Procedure Category ID]]="Covid Test",IF(COUNTIFS([Svc_Date],[@[Svc_Date]],[Patient ID],[@[Patient ID]],[Procedure Category ID],"Office Visit"),"X",""),"")

[CapRavOr]

This is actually what I was looking for! Can't believe I didn't think of that. Thank you!