Excel 2007 : Look for next previous max value based on date in column header

**jws1976** · 09-01-2011, 12:26 PM

I have a list of values, all under the column header of entered date

Person -- Jan 1 -- Jan 2 -- Jan 3 .......
Bob -- 6 -- 8 -- 10 ....
Alex -- 4 -- empty -- empty

what I need to do is, based on date and person, if the cell is empty or zero, find the next previous maximum value.

In the example above, if I asked for the maximum value for Alex on Jan 3rd, the returned value would be 4

If I asked for the maximum value for Bob on Jan 3rd, the maximum value would be 10.

**NBVC** · 09-01-2011, 12:37 PM

Assuming your table is in A1:D3 with headers in column 1 and row 1,

Then assuming you enter a name in A8 and a date in A9, try this formula:

Please Login or Register  to view this content.

adjust ranges and references to suit your actual data.

**jws1976** · 09-01-2011, 01:37 PM

worked like a charm, much thanks

**NBVC** · 09-01-2011, 01:47 PM

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save