max with condition

[rob2]

I have a spreadsheet which has an ID (in this case of a musical piece) in column C of tab “Lijst”.

Now I want to know when this is last performed by our choir and put that in a separate column.
The list of music performed is in tabs 2016, 2015, etc. where the ID is in column E en the performance date in column B.

I tried to put in:
{=MAX(IF(C2=‘2016’.$E$2:$E$150,‘2016’.$B$2:$B$150))}

That seems to work, but when I pull down this formula all cells report Err:508

How can I make this work?
Or is there an easier option to accomplish this?

[Lupp]

Keeping data of same structure in more than one sheets is a bad idea.
All the performce data in one sheet would allow to use the formula

{=MAX(($Performnces.$E$2:$E$5000=C2)*($Performances.$B$2:$B$5000))}   

entered for arry evaluation wit Ctrl+Shift+Enter.

I cannot see how your formula should return a “ltest” date" if vnot in 206.

Filling an array formula down with the mouse reuires modifiuction with the Ctrl key.

[rob2]

Lupp,
thanks for your answer.
Since I am on a Mac Ctrl doesn’t work to fill the arry formula down. Also the function key and the Applekey don’t do the trick. I also tried cut/paste, but that works only if pasted to a single cell. Any idea’s?

[Lupp]

First select the source cell and all the target cells, then, depending on the version (I’ not near to my computer; haven’t a LibO at hand at the moment):

Edit > … or
Sheet > …
Find a > ‘Fill’ > ‘Down’ menu item.