Add a MATCHIFS formula
Many power users and lightweight users alike have faced the situation where they need to find a row based on multiple criteria. E.g. find the row where column A = "John" and column B = "Doe". MATCHIF doesn't work on this unless you add a calculated column which concatenates the two, but that bloats and clutters the spreadsheet and increases risk of errors.
SUMIFS and COUNTIFS are wonderful formulas. A simple MATCHIFS formula which works similarly would solve the problem elegantly.
Akos Groller commented
Yes yes yes, MATCHIFS for President!
It's very common that I only need a single match (I know from data logic there's only one, or all are equivalent for the purpose I need the record). Or the field I need might be non-numeric, this useless for the otherwise highly appreciated SUMIFS, COUNTIFS, AVERAGEIFS.
Currently I need to add a RowNum field, pivot the data and extract Min(RowNum) as an intermediate step – OK, recalculates faster, but has memory overhead and does not allow for wildcards. (Concatenated key column, in turn, is just ugly...) When evaluation speed is not a major concern. MATCHIFS would be so much cleaner and more elegant.
This would be extremely useful
Srinivasa Bander commented
Agreed, As this will avoid process of concatenating to find matching value
Agreed. I need this to find the location of the number which was found by MAXIFS.
Michael Bishop commented
I'd love to have this function.
Charley Kyd commented
I agree! If we want to return text from a Table, MATCHIFS would provide an easy way to do it.
I know, there are workarounds. But they shouldn't be needed. Instead, we need MATCHIFS.
Kenneth Barber commented