How to use Indirect function in array lookup formula

Hello,

I am trying to incorporate indirect function in the below formula so that the formula always looks for same cells even though the reference cells in the master sheet are deleted or changed(when new cells are inserted in between). Any ideas on how I can accomplish it in the below formula?

Appreciate your help!

=IFERROR(INDEX(MASTER!$S$3:$BS$3,SMALL(IF(((MASTER!$CB4:$EB4)=(I$3))*(YEAR(MASTER!$S4:$BS4)=YEAR(I$2)),COLUMN(MASTER!$S4:$BS4)-COLUMN(MASTER!$S$4)+1),COLUMNS($I4:$I4)))," ")

Thanks in advance!

PM.

Thanks a lot! It worked. I learnt what I was doing wrong before, forgot "" for the cell ranges before.

Lookslike when we drag the cell with the formula Indirect("MASTER!$CB4:$EB4") to other cells(to copy the formula) it is not changing to Indirect("MASTER!$CB5:$EB5"). Is they way to get that with Indirect function?

Appreciate your help!

Thanks,

PM.

Thanks a lot! I need to drag down. It works. Appreciate your help!