Conditional IF formula using multi-dimensional arrays

  • Thread starter Thread starter iperlovsky
  • Start date Start date
I

iperlovsky

I am attempting to find a value in a 30 column (1000+ row) array that is
conditioned on finding an exact value match in adjacent column and a
not-to-exceed value in the same row in the 30 column array. If this were
limited to single column arrays, I would not have a problem, but the issue
arrises because I do not know which column the not-to-exceed value will be in
so I must use the full 30 column array. Here is my formula, which I am
hoping someone would be able to help me with:

{=MAX(IF((BI3:BI5000=A1)*(BJ3:CM5000<=A2),BJ3:CM5000))}

Thanks for your help,
 
Your formula should work. Here is my data - a little smaller than yours
4 1 28 1 54
39 2 48 53 21
x 3 34 19 56
x 4 37 98 27
x 5 50 56 15
x 6 67 72 12
x 4 82 96 47
x 6 49 62 12
x 4 24 98 42
x 5 34 67 72

The 4 and 39 are in A1 and A2 (the x's are to just pace holders)
I typed this formula
=MAX(IF((B1:B10=A1)*(C1:E10<=A2),C1:E10))
and completed it with CTRL+SHIFT+ENTER. Excel automatically enclosed it in
braces
{=MAX(IF((B1:B10=A1)*(C1:E10<=A2),C1:E10))} I DID NOT type the { }
The formula gave me the answer 37
There are three rows with 4 in column B, with these values in the other
columns
37 98 27
82 96 47
24 98 42
I want the largest of these that does not exceed 39 (value in A2)
Clearly this is 37.
If I change A2 to 50, the formula correctly returns 47.
This formula also gets the correct answer but does not require to be entered
with CTRL+SHIFT+ENTER
=SUMPRODUCT(MAX((B1:B10=A1)*(C1:E10<=A2)*C1:E10))

Have I read your question correctly?
 
Bernard, thanks for your help. Your explanation is very clear and it works
with my data set. I now realize a had a data quirk initially.

IP
 
Back
Top