Excel 2007 : Grabbing Cell in Same Column, Different Row

**The_Intern** · 12-22-2010, 04:38 PM

Howdy,
I am trying to obtain the date of occurance of a maximum value. Attached is the workbook I am working on. I tried to color coordinate so it is easier to understand my goal. The cell I am referring to has been marked red in there. If I use the HLOOKUP(MaxVal, TableOfTotals&Dates, Row#OfDates) aka [HLOOKUP(B139,B134:NB135,135)] it gives me an error. The end result I am looking for is to have the Date of that MaxVal occurance to be automatically obtained.

Is this the correct function I should be using or am I going the wrong direction?

Thanks,
The_Intern

EAA.xlsx

**NBVC** · 12-22-2010, 04:45 PM

Try:

=INDEX(B135:NB135,MATCH(B139,B134:NB134,0))

**jeffreybrown** · 12-22-2010, 04:47 PM

How about...

=INDEX($B$135:$NB$135,MATCH(B139,$B$134:$NB$134,0))

**The_Intern** · 12-22-2010, 04:58 PM

Both of those Methods worked flawlessly! Thank you so much, now I am off to understand HOW..

**NBVC** · 12-22-2010, 05:01 PM

Here is some help:

http://www.contextures.com/xlfunctions03.html

Note that if you have duplicate max values, the formula will pick up the first occurance.

**jeffreybrown** · 12-22-2010, 05:03 PM

Glad and it worked for you and happy hunting...

Index/Match

If you are satisfied with the answer provided please do not forget to mark the thread as solved.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save