How do I get to display the last date that has a corresponding value in another column in excel? [closed]

Closed. This question is off-topic. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 11 years ago.

Improve this question 开发者_运维百科

I have a excel spreadsheet with dates in one column, and results/values in another column (not next to each other, but on the same sheet). I want to set up a little summary table that will display the last date that had a corresponding result in the results column, since the latest date does not always have results available immediately. I would appreciate any help on this.

---

You can achieve this with an array formula.

=MAX(IF(B3:B12<>"";A3:A12;0))

Important with an array formula is that you finish entering by Control + Shift + Enter!! The formula will then be automatically enclosed in curly brackets. If you press only enter it will work as a normal formula.

What the formula will do:

The if will go through the range and if the cell content meets your condition then it puts the value from the according cell in the second array into his result array otherwise it puts in 0.

Then the MAX command is working on the resulting array. Important is that the arrays used in the array formula are the same size!