HELP : Minimum + Match maybe, with a bit of Index???

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

I have this table:

jon phil barry
1 56 5
2 58 9
3 59 8
5 47 100
9 58 569
8 59 58
45 26 4

In another cell(F3) it says "jon" and in the cell below this(F4) it
contains the formula "=MIN(A2:A8)" which is returning 1.

I'd like to amend the formula so that it returns whatever the minimum
number is below the name shown in F3 e.g if i change F3 to "phil" then
the formula in F4 will return 26.

Any help greatly appreciated

Jason
 
Name your table (say, Table):

=MIN(OFFSET(Table,,MATCH(F3,INDEX(Table,1,0),FALSE)-1,,1))
 
Jason

one way:

give each of the columns a name: select column A and in the Name Box, type
Jon; column B = Phil; column C = Barry.

Then, in cell F4, use the formula:

=MIN(INDIRECT(F3))

As you change the name in cell F3, it will reflect the minimum value for
that person.

I just named the whole column; you might want to restrict it depending on
what lies beneath.

Oh, and you might want to cater for the case where you type something that
doesn't exist:

=IF(ISERROR(MIN(INDIRECT(F3))),"Not defined",MIN(INDIRECT(F3)))

Regards

Trevor
 
=MIN(INDEX($A$2:$C$8,0,MATCH(F3,$A$1:$C$1,0)))

A2:C8 houses the figures, A1:C1 the names jon, phil, and barry, and F3 a
name for whom you want the min value.
 
Jason

looks like you have one or two options to choose from ;-)

Note though that they all need some error handling unless you can ONLY
select one of the names in cells A1 to C1. My approach would give a #REF!
if the name isn't there; the others will give you #N/A!

For example:

=IF(ISNA(MIN(OFFSET($A$2:$A$8,0,MATCH(F3,A1:C1,0)-1))),"Not
Defined",MIN(OFFSET($A$2:$A$8,0,MATCH(F3,A1:C1,0)-1)))

and:

=IF(ISNA(MIN(INDEX($A$2:$C$8,0,MATCH(F3,$A$1:$C$1,0)))),"Not
Defined",MIN(INDEX($A$2:$C$8,0,MATCH(F3,$A$1:$C$1,0))))

You could use Data | Validation | Settings tab | Validation criteria -
Allow: List; Source: =$A$1:$C$1 to restrict the input in cell F3.

Regards

Trevor
 
Since it is the MATCH function that will give the #N/A error there is no
need to go through the whole formula

=IF(ISNA(MATCH(F3,A1:C1,0)-1),"NotDefined",MIN(OFFSET($A$2:$A$8,0,MATCH(F3,A
1:C1,0)-1)))
 
Peo

thanks for the clarification. I've just got into the (perhaps bad) habit of
simply error checking the entire formula. I guess this is not as efficient
as it might be, although it requires less thought ;-) It does have a
tendency to make the resulting formula very long !

Regards

Trevor
 
Validation is a good idea. Another option is to use a 2-cell approach, which
is shorter and more efficient...

Y2:

=MATCH(F3,A1:C1,0)

X2:

=IF(ISNUMBER(Y2),MIN(INDEX($A$2:$C$8,0,X2)),"Not Defined")

X2 is the result cell.

[...]
I've just got into the (perhaps bad) habit of
simply error checking the entire formula. I guess this is not as efficient
as it might be, although it requires less thought ;-) It does have a
tendency to make the resulting formula very long !
Click to expand...
[...]
 
THANKS EVERYONE

I ended up using the following array formula:

{=MIN(IF(indexes=lowestindex,IF(datarange<>0,datarange,"")))}

indexes = the range containing the column headers
lowestindex = the range containing the column header that you wish to search
datarange = the range containing the table

(added a bit to avoid empty cells)

THANKS AGAIN

Jason


Aladin Akyurek said:
Validation is a good idea. Another option is to use a 2-cell approach, which
is shorter and more efficient...

Y2:

=MATCH(F3,A1:C1,0)

X2:

=IF(ISNUMBER(Y2),MIN(INDEX($A$2:$C$8,0,X2)),"Not Defined")

X2 is the result cell.

[...]
I've just got into the (perhaps bad) habit of
simply error checking the entire formula. I guess this is not as efficient
as it might be, although it requires less thought ;-) It does have a
tendency to make the resulting formula very long !
Click to expand...
[...]
Click to expand...
 
Why?

jason said:
THANKS EVERYONE

I ended up using the following array formula:

{=MIN(IF(indexes=lowestindex,IF(datarange<>0,datarange,"")))}

indexes = the range containing the column headers
lowestindex = the range containing the column header that you wish to search
datarange = the range containing the table

(added a bit to avoid empty cells)

THANKS AGAIN

Jason


"Aladin Akyurek" <[email protected]> wrote in message
Validation is a good idea. Another option is to use a 2-cell approach, which
is shorter and more efficient...

Y2:

=MATCH(F3,A1:C1,0)

X2:

=IF(ISNUMBER(Y2),MIN(INDEX($A$2:$C$8,0,X2)),"Not Defined")

X2 is the result cell.

[...]
I've just got into the (perhaps bad) habit of
simply error checking the entire formula. I guess this is not as efficient
as it might be, although it requires less thought ;-) It does have a
tendency to make the resulting formula very long !
Click to expand...
[...]
Click to expand...
Click to expand...
 
Back
Top