google-sheetsindexingmatchlookup
INDEX and MATCH return incorrect values in Google Sheets
There is a table in which each row is an event from the CRM system. Column A contains the IDs, and Column B contains the date and time of the event. Based on IDs, I need to calculate the duration between the next and previous events. No manipulation of tables.
| AMOCRM ID LEADS = 8952 | Date of change | Difference in hours |
|---|---|---|
| 2105687 | 2020-08-24 14:54:10 | |
| 2104881 | 2020-08-24 14:54:11 | |
| 2105687 | 2020-08-24 14:54:26 | 0:00:00 |
| 2105687 | 2020-08-24 14:54:28 | 0:00:00 |
| 2106409 | 2020-08-24 15:04:18 | |
| 2104881 | 2020-08-24 15:07:29 | 0:13:18 |
| 2117085 | 2020-08-24 17:07:51 | |
| 2117085 | 2020-08-24 17:07:53 | 0:00:00 |
| 2166941 | 2020-08-25 9:04:36 | |
| 2104881 | 2020-08-25 9:08:13 | 18:00:44 |
| 2104881 | 2020-08-25 9:08:14 | 18:00:45 |
| 2105687 | 2020-08-25 9:08:17 | 18:13:49 |
| 2106409 | 2020-08-25 9:09:45 | 18:02:16 |
| 2106409 | 2020-08-25 9:09:48 | 18:02:19 |
| 2117085 | 2020-08-25 9:10:17 | 0:00:00 |
| 2117085 | 2020-08-25 9:10:20 | 16:02:27 |
| 2170075 | 2020-08-25 9:49:11 | |
| 2106409 | 2020-08-25 18:32:54 | 9:23:06 |
| 2515935 | 2020-08-25 18:57:26 | |
| 2515935 | 2020-08-26 10:28:07 | 0:00:00 |
| 2560737 | 2020-08-26 15:27:34 | |
| 2117085 | 2020-08-27 12:25:40 | 51:15:20 |
| 2104881 | 2020-08-27 12:37:04 | 69:29:35 |
| 2117085 | 2020-08-27 15:24:54 | 54:14:34 |
| 2104881 | 2020-08-27 15:35:33 | 72:41:22 |
| 2515935 | 2020-08-31 14:54:42 | 0:00:00 |
| 2105687 | 2020-08-31 17:25:34 | 170:31:06 |
I tried this combination below and some others with the same logic
=IF(COUNTIF(A$2:A2, A2)>1, B2-INDEX(B$2:B2, MATCH(A2, A$2:A2, 1)), "")
but it doesn't work correctly in some cases. Here's the filtered sample:
Rows 3, 7, and 11 work. But in 12 (there should be 00:00:01) and 24 (there should be 51:28:50), it subtracts not the value from the previous event but from the 7 row. And in row 26 (there should be 02:58:29), it subtracts from the 3 row.
Solution
You may try:
=map(A2:A,B2:B,lambda(Σ,Λ,if(or(Σ="",Λ=""),,iferror(let(Γ,counta(A2:Σ)-1, Ξ,xlookup(Σ,offset(Σ,-Γ,,Γ),offset(Λ,-Γ,,Γ),,,-1),
if(len(Ξ),Λ-Ξ,))))))
- format the
Difference in hourscolumn toDuration - this works as arrayformula; so make sure the output column is devoid of any existing data/formulas so that the formula_output flows freely n' not throw an error
