I have a small Excel spreadsheet with 2 columns. One with values, the other with dates. Eg.

A     B
1   1/1/11

2   1/4/12

3   3/3/13

1   12/4/12

3   2/2/11

I want to

  1. search a value in column A (that value may occur multiple times) and get the row index where it occurs.
  2. get the largest (MAX) date from column B associated with the value that I searched for in column A using the search indices.

Example: I search for 1 and get back 12/4/12.

Ideally I would like to perform steps 1 and 2 in a single command. Help?


Setup your spreadsheet like this (only columns A & B)


Cell D2 is your input, so any number that is going to match in Column A

Cell D5 is your output,

Right-click cell D5 and select Format Cells, chose format as Date and then stick

=MAX(IF(A3:A7=D2, B3:B7))

Copy the formula to D5 and press CTRL+SHIFT+ENTER to use it as an array formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.