CODEWITHSUNDEEP
excelexcel-formulaarray-formulas
DMGregory
DMGregory

Reputation: 1379

Array formula to find the last matching condition

I'm trying to do something a bit monstrous with an array formula. I'm nearly there, but can't get the last piece in place.

Let's say I have rows of data like this...

 Property 1      Property 2      Property 3
 Orange          Square          Hot
 Blue            Circle          Cold
 Purple          Star            Slimy

And I have a table of criteria, consisting of a column to check and a value to match against:

 Property Index      Value
 2                   Circle
 1                   Purple
 3                   Slimy

For each row of data, I'm trying to identify the last criterion that is satisfied by the data on that row.

ie. for the three rows of data above, I expect the following outputs:

0 <- a hot orange square isn't slimy or purple or a circle
1 <- a cold blue circle matches (only) the circle criterion
3 <- a slimy purple star is both purple and slimy, 
     and we count the last match

So far I've attempted this array formula in a single cell:

 {=MAX(
     IF(
        INDEX(Data[@[Property 1]:[Property 3]], Criteria[Property Index])
        = Criteria[Value],
            ROW(Criteria[Value]),
            0
     )
  )}

The trouble is that as I step through the evaluation of the formula, the Criteria[Property Index] is evaluating to just the value of the first entry, 2, rather than to the whole column, {2, 1, 3}, so I just get the first criterion tested repeatedly.

I'd hoped to look up using this range of indices to produce the permuted lists {Square, Orange, Hot}, {Circle, Blue, Cold}, and {Star, Purple, Slimy} for each row respectively, to compare against the value range.

I've been able to get INDEX to use a range of values for the indices and return an array of each indexed result when the formula spans multiple cells, but I'm trying to avoid tying my sheet to a fixed length of the Criteria table. I'd like to be able to add more rows to it and have the formula still work.

Is there some way I can coax Excel to treat the index argument as a range in this case to get my desired array output? Or is there an alternate way I can perform this calculation that will still scale as users add/remove rows of criteria?

Upvotes: 1

Views: 223

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

It's doable, but there is a good idea to use a Named Range to encapsulate the strict Property columns of the Data table

=MAX(IF(
        INDEX(
              INDEX(PropertyData, 1 + ROW()-ROW($I$15), 0),
              1,
              N(IF({1}, Criteria[Property Index]))
        ) = Criteria[Value],
        ROW(Criteria[Value])-ROW(Criteria[#Headers]),
        0
))

PropertyData is the 1st cell in Property 1 column to the end via a Named Range.

PropertyData is defined named as:

=INDEX(Data, 1, COLUMN(Data[Property 1])-COLUMN(Data) + 1):INDEX(Data, ROWS(Data), COLUMNS(Data))

One hardcoded value is the $I$15 cell where the first formula lies. I put the tables and formulas out of sync to show it handles all well.

enter image description here

Upvotes: 1

Related Questions