Excel - EXCEL MAXIMUM VALUE AND VLOOKUP NESTED?

Asked By Edu on 03-Jul-07 09:36 AM
Hello,

I have a question I am hoping someone in here may be able to help me
with:
Say I have a range of data that looks like this:

A      B

0.1   0.6
0.2   0.9
0.3   1.4
0.4   1.0
0.5   0.6
0.6   1.0
0.7   1.3
0.8   1.1
0.9   0.9
1.0   0.4

If I want to obtain the maximum value in column B for a range of 2
numbers correcponding to column A, which formula should be used?

e.g. For values between 0.4 and 0.8 corresponding to A, the max in the
range in column B is 1.3.

Any help would be very appreciated.

Thanks,
Edu




vezerid replied on 03-Jul-07 09:50 AM
Edu,
use the following *array* formula (commit with Shift+Ctrl+Enter)

=MAX(IF((A1:A10>=0.2)*(A1:A10<=0.4),B1:B10))

HTH
Kostis Vezerides
drille replied on 03-Jul-07 09:54 AM
assuming on..
A1 = min_search_value = 0.4
A2 = min_search_value = 0.8
A3:B12 contains your sample data..

then you may try something like this...on B1
array formula with CSE
{=MAX(IF((A3:A12>=A1)*(A3:A12<=A2),B3:B12))}

regards,
driller
--
*****
birds of the same feather flock together..
Teethlessmam replied on 03-Jul-07 10:10 AM
=SUMPRODUCT(MAX((A1:A10>=0.4)*(A1:A10<=0.8)*B1:B10))
drille replied on 03-Jul-07 10:16 AM
maybe not this *array* formula with CSE

{=MAX((A3:A12>=A1)*(A3:A12<=A2)*(B3:B12))}


--
*****
birds of the same feather flock together..