I have an index match formula I need some help with. The cell I enter the formula in returns the correct value so I know it is working but for some reason when I drag it down it just returns the same value as the first cell. Even when I use the Cntl + Shift + Enter it does the same thing. Is there something I am missing with using my formula? Please see formula below.

=INDEX('Sheet2'!A:B,MATCH(Y2,'Sheet2'!A:A,0),2)

Sheet2 = Lookup values where column A is the list taken from column Y on sheet 1. Column B is what I want to return.

Any advice on what I am doing wrong is greatly appreciated. Please let me know if more information is needed.

Thanks,

Mychal