2

I know this is possible using a combination of probably =FILTER, ARRAYFORMULA and probably QUERY but hopefully, the examples will explain it better than words can.

Testing Data

Effectively, I have an output (the top table) and I want the formula that will bring back the dates each person has a mismatch (bottom table)

I hope that's clear enough.

player0
  • 124,011
  • 12
  • 67
  • 124
Adam Lough
  • 23
  • 2

1 Answers1

1

try:

=ARRAYFORMULA(QUERY({A2:A10, TRIM(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B2:G10="mismatch", B1:G1, )),,999^99)), " "))}, "where Col2 is not null", ))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    damn, this is some formulae magic right there. Nice one mate! – Umar.H Nov 19 '19 at 19:03
  • 1
    What a hero, i knew it was a combination of them but couldn't for the life of me fathom out which way to use them - superb work very much appreciated – Adam Lough Nov 20 '19 at 09:05