Advanced Index(Match) or lookup formula for unformatted data set-CodePudding

My problem is most easily explained with this example: https://docs.google.com/spreadsheets/d/1_hXhl3fTfNuYG--hIvFVV7SKdwriROtYvHPLa6QpBYY/edit?usp=sharing

In it, I have a "CurrentData" tab which is, the current data set. I am trying to transpose the values for each matching of Target (Column A) and Category (Row 2) into the "CleanData" tab.

I have copied over the desired results for the "Leads" data set as an example.

I have tried some multi-criteria Index(Match) formulas, but I get an error saying it must be one row or column.

I am at a loss for what to do, and as the actual sheet has a year's worth of data and twice as many data points - I am hoping that someone can save me from the manual copy/paste of one value at a time for thousands of cells...

Thanks!

CodePudding user response：

As for generating a dataset you can reference, try this out

=ARRAYFORMULA(
  QUERY(
   IFERROR(
    SPLIT(
     FLATTEN(
      IF(ISBLANK(CurrentData!A3:A),,
       CurrentData!A3:A&"|"&CurrentData!B2:G2&"|"&CurrentData!B3:G)),
     "|",TRUE,FALSE)),
    "select Col2, Col1, Col3
     where Col3 is not null",0))

if all goes to plan, it'll output three columns like your cleanData sheet. From there you can use a normal filter -- for example, if you wanted General and Leads... lets assume this output is in a second sheet in A:C and you have data validation in E1 (General) and E2 (Leads)

=FILTER(A1:C,A1:A=E1,B1:B=E2)

or if you wanted to get fancy and have a general filter, this would let you choose a value for either the first or second column or both

=IF(COUNTA(E1:E2)<1,,
  FILTER(
   A1:C,
   REGEXMATCH(A1:A,IF(ISBLANK(E1),".*",E1)),
   REGEXMATCH(B1:B,IF(ISBLANK(E2),".*",E2))))

If E1 or E2 is blank, it returns a wildcard, which will give you everything. The COUNTA part forces the formula to wait until there is at least one input, otherwise it'll simply return your entire dataset.