Pick the greatest number in a range that is also less than the corresponding number in another range's row

user_782564 0
Viewed 136

I have two sets of data in columns A and B. I would like to pick the maximum value from column A which is also less than the value in the corresponding row in column B. I think I ought to be able to do this with the MAXIFS function but all the examples I can find compare against static values. I tried these options

=MAXIFS(A1:A10, B1:B10, "<")
=MAXIFS(A1:A10, B1:B10, A&"<"&B)

but neither of them worked as expected. In the first case, it is always 0 suggesting the condition is never met, in the second it gives an error.

I know that I could do this by creating a separate region of cells which first filter out the data that doesn't match the conditional and then simply pick the max from what remains but I'd rather do it in a single cell if possible.

Is there a syntax for this comparison and, if so, what is it?

google-sheets-formula formula google-sheets
2 Answers

To the best of my knowledge there isn't a way of getting it to work with MAXIFS.

You can write this

=maxifs(A:A,A:A,"<"&B:B)

and it will accept it, but it just uses the first value in column B and doesn't do a side-by-side comparison.

So you have to do it another way e.g. with a combination of Max and If:

=ArrayFormula(max(if(A:A<B:B,A:A)))

or you can use max with a filter or query:

=max(filter(A:A,A:A<B:B))

=max(query(A:B,"select A where A<B"))
user_38949170 
=ARRAY_CONSTRAIN(ARRAYFORMULA(
 IF(LEN(INDIRECT(ADDRESS(ROW(), COLUMN(B:B))))>0,
 IF(INDIRECT(ADDRESS(ROW(), COLUMN(B:B)))<MAX($A$1:$A),
 MAX($A$1:$A), LARGE(UNIQUE($A$1:$A), 2)), )), 1, 1)

4

user_56326290
Related