returning offset row from excel table

D

destrolennox

Friends,

I am working on an excel project at my office. I have a table set u
with 12 Months (Jan-Dec) running across the top, and usernames runnin
down the left. The data inside the table consists of each users tota
spending within the given month.

Right side, (outside of the table) I want to start gatherin
information. By using the MAX function "=MAX(B5:O5)" I was easily abl
to calculate the max monthly amount spent in the year. However, I als
want to return the name of the month in which they spent this amount.
So, I want to return the column in which the amount was spent (jus
like in the max function) but offset the returned row to be the ro
containing the DATE information that runs across the top of the table.


I hope that makes some sense, any ideas?

I have messed around a bit with the COL and ROW functions, but it seem
that you cannot put in a function within these functions. For example
it will correctly return =COL(B5) but I need it to return somethin
similar to =COL(MAX(B5:O5)) which it will not return (gives an erro
and wont let me use the formula.

I have also looked at the OFFSET function, but it seems that the sam
COL problem exists.

Thanks for the help,

Brenda
 
G

Guest

Assuming your MAX function formula is in cell P5, and you want the result of
the corresponding month to show in cell Q5:
=INDEX((B1:O1),MATCH(P5,B5:O5,0))
Although I am not sure why your range is 14 cells when there are 12 months.

-Simon
 
B

Biff

Hi!

If you have the 12 months listed starting in column B then the 12th month
should be in column M.

Assume the month names are in row 1, B1:M1.

=INDEX(B1:M1,MATCH(MAX(B5:M5),B5:M5,0))

Note: if there is more than one instance of MAX the formula will match the
first instance.

Biff

"destrolennox" <[email protected]>
wrote in message
news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Offset with Vlookup 2
Find values across a row and return a text value from the same column 2
Returning the last value in a row 4
Why does this confuse me?? -- ? on Offset function 1
Excel functions - need some guidance. 3
Pivot Table: Return Value From Same Row 5
Use of OFFSET and LOOKUP to find a value in a table 6
Pivot Table Question 4

Top