CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Andrew Gonzales
Andrew Gonzales

Reputation: 15

How to return date values from an arrayformula in an index:match formula (left/right function)

I'm trying to use an index-match function from a text string that was converted from an Array. This is the spreadsheet I'm working on, in row M I'm trying to return a date value from a linked spreadsheet (metabase data), that has the dates that the linked "assignments" started on.

0

The metabase data spreadsheet is as follows:

As you can see in the first image, I've been using left and right function to cut out the last 4 numbers of each assignment, before the last hash, and match that up with the assignment ID in the metabase sheet. I think because it's a query when I just matched to column A in the metabase sheet, I got N/A, but when I used an array formula, I got the assignment IDs, and the repeated 9/10/2018 dates were returned in the index:match function. I moved the array formula function to row 1299 for better visibility, but there were no changes from when the array formula was on line 2.

=index('metabase data'!Q:Q, MATCH(left(Right(K2, 5), 4),'metabase data'!S:S),false)

I'm hoping for the Start Date column to return the start dates that are in the metabase sheet, corresponding to the assignment IDs

Upvotes: 1

Views: 467

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(TO_DATE(IFERROR(VLOOKUP(VALUE(LEFT(RIGHT(K21:K, 5), 4)), 
 {VALUE('metabase data'!A:A), 'metabase data'!Q:Q}, 2, 0))))

0

Upvotes: 1

Related Questions