Hi,
I'm having trouble writing a formula that matches a name from one sheet where there is only one row person to another sheet with multiple rows per person. It is also contingent on their response to question.
For example, I have two workbook sheets. In A, one person could be listed on multiple rows for every check in (different dates) they have. They may or may not have an arrest at each check in. I have calculated the number of days from their entrance into a program to their arrest (if they have one) for the check in on that date.
In B, there is only one row per person and it aggregates things like the # of arrests.
I want to create a column in B that brings in the number of days from the persons' start of a program until their first arrest. This requires getting the days from A, but only selecting the first time they had an arrest.
This is the formula someone used for another column. I want to use something like this, but make it contingent upon whether they had an arrest. This only brings in the first line with their name, and they may not have had an arrest at that point. So, it shows as 0 days. If cell R4 is "Yes" (meaning they had an arrest, where should I put the IF(R4'"Yes") statement so that the formula only looks at the first date that they had an arrest?
I hope this makes some sense. Nothing is working, but it seems like it should be simple.
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('7 - Supervision'!$L$4:$L$100;MATCH(A4;'7 - Supervision'!$A$4:$A$100;0));"")); 1; 1)
Bookmarks