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 Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"iperlovsky" <(E-Mail Removed)> wrote in message
news:69D0C3C4-7BE0-4F44-AC0F-(E-Mail Removed)...
> 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,
>

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

"Bernard Liengme" wrote:

> 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 Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
> "iperlovsky" <(E-Mail Removed)> wrote in message
> news:69D0C3C4-7BE0-4F44-AC0F-(E-Mail Removed)...
> > 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,
> >
> .
>