hlookup or index/match?

  • Thread starter Thread starter Wilburn
  • Start date Start date
W

Wilburn

Sorry for the rather odd subject line but I wasn't quite sure how to pu
it, or which one would be the one I need (either the hlookup functio
or a combination of match/index).
The situation is the following :
I have a worksheet with the following (basic) data :
B3 to M3 : months of the year (januari to december)
B15 to M15 : Net income amount for the particular month.

I used the MAX formula in cell E17 to locate the highest net incom
amount in the row B15 to M15, which worked fine.

What I am trying to do next is the part that stumps me, I can't believ
its impossible, but it doesn't seem to be easy either, unless I'm doin
things wrong, which is probably the case.

In cell D17 I want to show the name of the month from the row B3 to M
that corresponds with the result of the MAX formula in cell E17, th
highest value in the row B15 to M15.
I tried using HLOOKUP but wasn't quite able to make it work.
I tried an index/match combination, but I could only get that workin
if I'd input the month as well. (the month column and the net incom
row).
I'd rather not do it that way as if the values/amounts might change
the month with the highest net income would/could change as well, s
the month that I'd have to put in the index/match combination migh
then not be the month I need.

I hope this sort of makes sense, my way of describing things is almos
as confusing as this problem makes me feel.
:confused:

Thanks in advance for any hints/tip
 
One way:

=INDEX(B$3:M$3,MATCH(E17,B15:M15,FALSE))

or, all in one

=INDEX(B$3:M$3,MATCH(MAX(B15:M15),B15:M15,FALSE))
 
JE McGimpsey : Thanks a whole lot for the formula.
I made some attempts at using match/index, which I left on my workshee
to fiddle around with and they look almost the same but I made a smal
mistake.
I think I got a little annoyed that it didn't work and overlooked th
minor details.
It works really well now though, thanks once again.
Aladin : thanks to you as well for your URL!
When I looked at McGimseys solution I noticed what I did wrong s
didn't need your URL for that particular problem, however when
browsed through the page you mentioned I saw several othe
formulas/functions I haven't used before but which I might end u
needing in the long run, having a page with hints and tips at hand i
great in case I wander into problems (again)
 
[...]
Aladin : thanks to you as well for your URL!
When I looked at McGimseys solution I noticed what I did wrong so
didn't need your URL for that particular problem, [...]
Click to expand...

If multiple instances of a Max value are associated with different months,
you'll need for sure the approach the link describes.
 
Back
Top