Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

Searching within the result of a vlookup using a range of values and parsing text

MY GOAL:

parse a MM/DD date from the result of a vlookup so that it can be used in a project plan

BACKGROUND:

  • The vlookup result contains multiple values separated by a "•" (I don't need all of them)
  • The value I'm looking to parse is not always in the same location in the vlookup result (otherwise I could use the RIGHT formula)
  • There is a finite number of the values I'm looking to retrieve (and I know them already)
  • The value that I'm looking to retrieve contains some text with a date range; I only want the first four values in the date range (MM/DD)
  • I'd like to achieve all this with a single formula with the result in a single cell

CURRENT FORMULA

  • The formula that I've been working on that is not working is:

    =ARRAYFORMULA(if(iserror(search(Iterations!D2:D7,(VLOOKUP(A2,'Results {2596503}'!$C$2:$L$183,3)))),,))

Solution

  • I've set up a sheet called "Erik Help" with the following formulas in B2 ad C2:

    =ArrayFormula(IF(A2:A="","",MID(VLOOKUP(A2:A,data!A2:B,2,FALSE),FIND(REGEXEXTRACT(VLOOKUP(A2:A,data!A2:B,2,FALSE),"[0-9]-[0-9]"),VLOOKUP(A2:A,data!A2:B,2,FALSE))-4,5)))

    and

    =ArrayFormula(IF(A2:A="","",MID(VLOOKUP(A2:A,data!A2:B,2,FALSE),FIND(REGEXEXTRACT(VLOOKUP(A2:A,data!A2:B,2,FALSE),"[0-9]-[0-9]"),VLOOKUP(A2:A,data!A2:B,2,FALSE))+2,5)))

    respectively.

    They may be longer than actually needed, but you did not share realistic results in Column B or list which symbols may appear in Column B other than in the date; so I tried to account for either a hyphen or a forward slash possibly appearing in Column B in places other than within the date span.

    Your analytics sheet also shows a formula that is sorting the results from data!A:A. So even though in your example the original data order happens to be the same as in analytics!A:A, that is not a given (again, based on your formula). Therefore, the VLOOKUP is also necessary.

    You did not indicate whether you need to further use these returned date-snippets in calculations, or whether you just need to view them. So the results generated in "Erik Help" are text.

    If you want usable numbers/dates, you add further issues that would need to be controlled for in the formula, because you'll only be extracting month and day, not year. That's fine right now. But what about when the date range to be extracted is "12/28-01/13"? If you simply make these values/dates, they will both be assigned to the current year. So the end date here will wind up being earlier than the start date.

    Because of this, I've added a second sheet, "Erik Help 2," which contains extended formulas to account for these cases while still returning the date format you want as actual dates which can be used in calculations.