# How do I calculate the max from two arrays with a year value?

Hello

I have asked a similar question before and need some additional help:

As you can see, there are dates in column A and this time, I'd like to display the favourite shop for, lets say, 2011. What do I need to change to the original formula of

``````=INDEX(Shop,MODE(IF(Customer=F2,IF(Shop<>"",MATCH(Shop,Shop,0)*{1,1}))))
``````

Thanks
Massimo

mode-by-year.xlsx
mode-by-year.PNG
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
If you enter the year number in I1 then you can try this formula

``````=IFERROR(INDEX(Shop,MODE(IF((Customer=F2)*(YEAR(DateVisited)=\$I\$1),IF(Shop<>"",MATCH(Shop,Shop,0)*{1,1})))),"")
``````
InternshipAuthor Commented:
Hi

I've entered the formula but I get empty cells .. I've attached the spreadsheet.
Did I miss something?

Massimo
mode-by-year-problem.xlsx
EngineerCommented:
Maybe you are not entering this as an array formula. When you enter the formula in the cell press F2 then press shift-ctrl-enter
See attached
Copy-of-mode-by-year-problem.xlsx

Experts Exchange Solution brought to you by