Formula to find the highest number in a row.

I am returning to Excel after a long absence and hope someone can help me with what i think is a simple problem

I made an expense spreadsheet that adds up what i spend each day and what i spent at the end of the month

I want to format a cell to tell me what "Category" say "Groceries" has the highest amount and what has the lowest.

I was thinking that the =HLOOKUP function was the way to go, but can't seem to get it right..

What's the deal ? am i at least in the right ballpark ? thanks for any help.

Can you give us an idea of how your data is setup and some sample expected inputs/results?

Thanks for replying NBVC, i took sometime off to make the page simpler..the data set up is a simple date, budget and spending outcome, with totals going both vertical and horizontal.

Here is an upload that should explain it better...

To get the column Header with the largest amount try:

=INDEX($C$2:$G$2,MAX(IF($C$3:$G$8=MAX($C$3:$G$8),COLUMN($C$3:$G$8)-MIN(COLUMN($C$3:$G$8))+1)))

this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You should see { } brackets appear around the formula if done correctly.

It seems possible that the MAX amount might appear more than once though. If so, and you want to list all the column headers that have that max number, then change formula to:

=IFERROR(INDEX($C$2:$G$2,SMALL(IF($C$3:$G$8=MAX($C$3:$G$8),COLUMN($C$3:$G$8)-MIN(COLUMN($C$3:$G$8))+1),ROWS($B$12:$B12))),"")

again, confirmed with CTRL+SHIFT+ENTER instead of just ENTER. Then copy the formula down as far as you want, you should see either more column headers or blanks.

To get the smallest, it would be similar formulas, replacing all the the MAX functions with MIN.

Thanks ! i knew it..it was an array solution. Thanks again.

Which version/edition of Excel are you running? I wouldn't recommend you keeping data structured like that. It makes for a bad idea when trying to get information out of it. I've used Power Query to restructure your data in the attached file, on Sheet 2. From there, since the data was in a table, I used a PivotTable to summarize the data, and then put conditional formatting on it. This is a sortable and filterable summarized table (aka PivotTable). To get any meaningful data out of the structure you had will always need some fancy formulas like the above posted.

LOL, I am a fancy formula type of guy, you know eace:

LOL! I'd say "fancy" was an understatement. I'd say "genius", but I wouldn't want it to go to your head!