Forum Discussion
Lookup another row with multiple criteria
Okay, so I've hit a wall. I'm hoping some excel wizard out there can help me.
I am trying to search an entire excel document for any row that matches all 4 criteria, and display True or False in a calculated column. (a formula in a single cell that I can apply to an entire column).
So basically, I'm checking to see if a patient at our medical practice completed a therapy appointment the same day as their medical appointment. The formula needs to display true if any other row on the sheet matches these criteria:
- Is "therapy" in column A
- Is the same date in column B as the row with the formula
- Is the same patient number in column C as the row with the formula
- The visit was completed, shown in column D
I'm only doing this calculated column for medical appointments (rows 2-5). You can see the answer key on the sheet. For therapy appointments, I'm just going to use the same formula but search for "medical" in column A instead of therapy. I could also display N/A and that would be fine.
What's the best option here? I've been trying different variations of XLOOKUP and I can't get it all the way there, keeping in mind that the formula has to be able to be dragged down through tens of thousands of rows on my workbook.
In D2:
=IF(A2="medical",COUNTIFS($A$2:$A$100000,"therapy",$B$2:$B$100000,B2,$C$2:$C$100000,C2,$D$2:$D$100000,"Yes")>0,"N/A")
Fill down.
4 Replies
I have used COUNTIFS to determine whether the record you require is present or not
= IF([@VisitType]="medical", COUNTIFS( [PatientNum], [@PatientNum], [VisitDate], [@VisitDate], [VisitType],"therapy", [VisitComplete],"yes"), "" )The data is entered as an Excel Table and I also used number formatting to convert the {1,0} to "Yes"/"No".
- Thanks Peter, that's the solution that Hans gave me above. That's definitely simpler than what I was trying to do. Thanks for the alternative script
In D2:
=IF(A2="medical",COUNTIFS($A$2:$A$100000,"therapy",$B$2:$B$100000,B2,$C$2:$C$100000,C2,$D$2:$D$100000,"Yes")>0,"N/A")
Fill down.
- That's a simple fix. Thanks so much!
