How do I make a sort of INDEX MATCH with condition in Excel?-CodePudding

I want to search IF the value of MONDAY(row) and 1(column) equals T. If not, it checks the one below it until the value equals to T. However, when I use normal index match, it only returns the value 0 of the first MONDAY-1 (B2) instead of the second MONDAY-1 (B3).

CodePudding user response：

If you want to search only for the Column 1 (that is what you have in your question), then you can use the same above formula, but just reducing the range to just the first column (B2:B18), but the logic remains the same

CodePudding user response：

Try this on cell Q2 (I am not sure about your question unless you provide a sample output), if you want to calculate of the T values for MONDAY.

=IF(P2="","",SUM(--(ISNUMBER(XMATCH(FILTER(B2:M18,A2:A18=P2),{"T"})))))

Note: If you want to search only for the Column 1 (that is what you have in your question), then you can use the same above formula, but just reducing the range to just the first column (B2:B18), but the logic remains the same:

=IF(P2="","",SUM(--(ISNUMBER(XMATCH(FILTER(B2:B18,A2:A18=P2),{"T"})))))

If you want to search for T values for MONDAY VLOOKUP or XLOOKUP is a good option, because it stops at the first match if not it continues. You can use this:

=LET(search, 
 XLOOKUP(A2:A18&B2:B18,P2&B2:B18,B2:B18, ""), 
 IF(search=0,"", search))

Note: We concatenate the first two columns to find only for MONDAY and T. It returns an array with empty values except at the position the T was found and returns T.

Sample output for counting all Ts for MONDAY:

Explanation

You cannot use COUNTIF, because all the input ranges must have the same shape (rows and columns):

=COUNTIFS(B2:M18,"T",A2:A18,P2) -> #VALUE!

You cannot use FILTER as input range of COUNTIF, that is why I use XMATCH.

SUM(--(ISNUMBER(XMATCH(FILTER(B2:M18,A2:A18=P2),{"T"}))))

Filter the range by MONDAY (P2), it will return a 2x12 array with T and ceros. We use XMATCH to identify where we have a T. In cells T is not found #N/A otherwise returns 1. We convert the result to TRUE/FALSE with ISNUMBER and then via -- operator transform it to 1/0 values. Then wherever we have 1 there was a T, so SUM the resulting range gives the total of Ts for MONDAY.

The IF at the beginning is just to allow expanding the formula down.