using two functions in the same cell

[Brian204845]

OK here's the problem. Asignment in school due tomorrow. I have to use the
MIN function to find the lowest expense in a row of three cells (B38 C38 and
D38). No problem thee =MIN(B3838). But I have to embed that function
within the LOOKUP function in order to find the matching column label (Year 1
Year 2 and Year 3) which are in cells B32 C32 and D32. I have to print
"minimum expense year" under the the appropriate cell with the lowest
expense. The cells immediately below the three cells with the expense
numbers are B39 C39 and D39. I can't seem to get this right. Any
suggestions would be appreciated! Brian

 

[T. Valko]

Not real sure what you're wanting but here's one way to get the column
header:

Year1...Year2...Year3
...50.........27........99

="Year"&MATCH(MIN(B3838),B3838,0)

If your column headers aren't really named "Year_n":

=INDEX(B3232,MATCH(MIN(B3838),B3838,0))

I have to embed that function within the LOOKUP function

The LOOKUP function *requires* the lookup_vector be sorted in ascending
order. If it's not sorted it won't work properly.

 

[Brian204845]

Thanks Biff. The assignment question is this, "Find the lowest total exoense
amount and print the message 'minimum expense year'under the cell where the
lowest total expense occurs." I can use the MIN function to find the lowest
value easily enough but the problem is then to get the formula to print that
message. Perhaps I'm looking too hard using the LOOKUP function. Can that
minimum function be embedded in another function to print that message in the
cell below the lowest total. The colums are indeed titled Year 1 through 3
left to right.

 

[Brian204845]

Here's what I came up with but the syntax ia wrong.
=LOOKUP(MIN(B3838),B3232,B3939)

 

[T. Valko]

Try this in B39 and copy across to D39:

=IF(B38=MIN($B38:$D38),"minimum expense year","")

I don't know how "invovled" your assignment is but some things to consider:

If *all* the cells in B33838 are empty the formula will return the message
in each cell B3939

If there are multiple instances of the MIN, each instance will return the
message.

 

[Brian204845]

Thanks Biff! That worked. I now see how it works!

 

[T. Valko]

You're welcome. Thanks for the feedback!