Min formula not returning value from Index
 

I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value
that arises from the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that
is neither E38 nor the value from the Index. It seems to
be pulling a value from the array within the index. Can
I wrap something around the index to ensure that only the
values from E38 and the Index are included in the calc.

Thanks

I just created an array of the same dimensions you used and tested with some
different values and it works fine? I assume you know that if you use zero in
the index formula it will return an array of values from the same row as the
match, thus if the match returns 7 index will return an array of all values
in E27:W27 thus min will pick among all those values and the value in D38. If
you meant to just compare D38 with one value you need to specify the column
number with anything 0 in INDEX

Regards,

Peo Sjoblom

"ExcelMonkey" wrote:

I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value
that arises from the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that
is neither E38 nor the value from the Index. It seems to
be pulling a value from the array within the index. Can
I wrap something around the index to ensure that only the
values from E38 and the Index are included in the calc.

Thanks

The INDEX bit does not specify a single value. From E to W, where do you
want INDEX return a value?

ExcelMonkey wrote:
I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value
that arises from the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that
is neither E38 nor the value from the Index. It seems to
be pulling a value from the array within the index. Can
I wrap something around the index to ensure that only the
values from E38 and the Index are included in the calc.

Thanks

Actually if you array enter it using 19 cells across it will return the
whole row

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Aladin Akyurek" wrote in message
...
The INDEX bit does not specify a single value. From E to W, where do you
want INDEX return a value?

ExcelMonkey wrote:
I have a formula that looks like this:

=INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0)

I want to wrap a Min formula around the result of value that arises from
the formaul and another cell E38:

=Min(E38, INDEX($E$21:$W$35,MATCH($B40,$B$21:$B$35,0),0))

However, when I do this, it seems to produce a value that is neither E38
nor the value from the Index. It seems to be pulling a value from the
array within the index. Can I wrap something around the index to ensure
that only the values from E38 and the Index are included in the calc.

Thanks