How to use index match formula that automatically expands in Google Sheets

0
votes

In Google Sheets I am using a filter function to pull in Names into column A and a Timestamp into column B. Every time a second occurrence of the name shows up into columns A & B of the list I want column C next to the prior occurrence to reference the new timestamp. In column D I will then calculate the difference from the names timestamp and the next occurrence of that same name.

Currently I am using the following formula:

=IFERROR(INDEX(B3:B,MATCH(A2,A3:A,0)))

If I drag this formula down it does what I need it to do, but due to how many rows are being added to the first two columns, rows are being added to the bottom of the sheet due to the filter and the formulas keep needing to be dragged down. The durations in column D are being calculated with the following formula, that automatically arrays the results and automatically expands with the filter results:

=IFERROR(ARRAYFORMULA(IF(C2:C="","",C2:C-B2:B)))

I would like my index match formula to do the same, but it seems I cannot use the index formula with an arrayformula.

I attempted to achieve this by using a vlookup combined with an offset for the range. The first row is giving me the result I want, but all the subsequent rows are not referencing the offset range, probably because the offset isn't changing with each new array result here is that attempt:

=IFERROR(ARRAYFORMULA(VLOOKUP(A2:A,OFFSET(A2:B,1,0),2,FALSE)))

Any ideas how this could be accomplished by placing a formula in one cell, or would this have to be accomplished with a script?

I have added an example spreadsheet of the current method HERE

Thanks in advance for any help.

1
google-sheets

1 Answers

1
votes

Formula

Instead of 

INDEX, MATCH and OFFSET

try the following formula

=ArrayFormula(IFERROR(VLOOKUP(
TRANSPOSE(VALUE(REGEXEXTRACT(QUERY(TRANSPOSE(
IF(FILTER(ROW(A2:A),LEN(A2:A))<TRANSPOSE(FILTER(ROW(A2:A),LEN(A2:A))),
IF(FILTER(A2:A,LEN(A2:A))=TRANSPOSE(FILTER(A2:A,LEN(A2:A))),
TRANSPOSE(FILTER(ROW(A2:A),LEN(A2:A))),
),)
),,2000000),"(\d+)"))),
FILTER({ROW(A2:A),B2:B},LEN(A2:A)),2,0)))

Formula description

This part creates a square matrix showing the row number of the value that matches if it's below of the current row:

IF(FILTER(ROW(A2:A),LEN(A2:A))<TRANSPOSE(FILTER(ROW(A2:A),LEN(A2:A))),
IF(FILTER(A2:A,LEN(A2:A))=TRANSPOSE(FILTER(A2:A,LEN(A2:A))),
TRANSPOSE(FILTER(ROW(A2:A),LEN(A2:A))),
),)

This part takes the smallest row that matches the current row (the next occurrence of the row value)

TRANSPOSE(VALUE(REGEXEXTRACT(QUERY(TRANSPOSE(  ),,2000000),"(\d+)")))

This part returns the related value, if any, otherwise a blank:

IFERROR(VLOOKUP(  ,FILTER({ROW(A2:A),B2:B},LEN(A2:A)),2,0)))