excelexcel-formuladynamic-arrays

How to make a spilled array formula in Excel with MATCH or XLOOKUP that do not skip rows

I have generated a spilled array function that returns a value if two conditions are met. I have a raw data table with all my data, and then I have "my table", which is the table where I want to gather all the relevant information.

I have two different functions that almost work in different ways:

  1. The following function skips the rows that are FALSE, resulting in my values being misplaced relative to the two listed conditions. So the first value in AR, is actually belongs to the second row of the table and not the first, which is a problem.

    =FILTER(AN$6:AN$15; ISNUMBER(MATCH($AL$6:$AL$15; $AP$6:$AP$15; 0)*MATCH(AM6:AM15; AQ6:AQ15; 0)))

The function is used in column AR.

The function is used in column AR

  1. Alternatively, I have a drag function that does not skip the FALSE rows. However, this require a Drag Down, which is not practical. But I do not know how to make this function to a dynamic SPILL function.

    =IFERROR(XLOOKUP(1;($BA$6:$BA$11=BG6)*($BB$6:$BB$11=BH6); $BC$6:$BC$11); "")

The function is used in column BI and BJ, where BI shows the function working when draged down, and BJ is where I want the function to work without a need to drag down. The function is used in column BI and BJ

Solution

  • This formula returns 0 (or whatever you want it), to non matching columns:

    =IF(ISNUMBER(MATCH($BA$7:$BA$12;$BG$7:$BG$12;0)*MATCH($BB$7:$BB$12;$BH$7:$BH$12;0));BC7:BD12;0)

    Change the last 0 to any value, comment (within " ") as you like, or just "" for 'empty' cells.

    Results

    EDIT:

    This should fix the random order issue:

    =IF(ISNUMBER(MATCH($BG$7:$BG$12;$BA$7:$BA$12;0)*MATCH($BH$7:$BH$12;$BB$7:$BB$12;0));HSTACK(XLOOKUP($BG$7:$BG$12;$BA$7:$BA$12;$BC$7:$BC$12);XLOOKUP($BG$7:$BG$12;$BA$7:$BA$12;$BD$7:$BD$12));0)

    Note that the order of arguments in the two MATCH functions has changed, so now you're looking up your output table, in your raw table (it was the other way around).

    Also found this out: Problem? I'm not sure what's supposed to happen in this situation, if it can even occur.

    When in your output table the from -to doesn't match ID but still makes a match, you'll still get a result (from the from column).

    EDIT2: Third time is the charm?

    =IF(ISNUMBER(XMATCH($BG$7:$BG$12&$BH$7:$BH$12;$BA$7:$BA$12&$BB$7:$BB$12));HSTACK(XLOOKUP($BG$7:$BG$12;$BA$7:$BA$12;$BC$7:$BC$12);XLOOKUP($BG$7:$BG$12;$BA$7:$BA$12;$BD$7:$BD$12));0)

    This will check for exact matches of the down and up column, so non-matching gets '0'.

    Final Fix?