Issue with copying index & match formula across columns

  • Hi there,


    I've been using an index & match formula which is working perfectly across the first six columns, but won't drag or copy to the other columns. A #REF! result occurs. I've even typed the formula in manually and get the same result.


    I've searched online for possible causes but haven't found a solution.


    The formula is:


    =INDEX('source'!$B$4:$GZ$521,MATCH($A15,'source'!$A$4:$A$521,0),MATCH(L$14,'source'!$B$3:$GZ$3,0),MATCH(L$13,'source'!$B$2:$GZ$2,0))


    The formula has three criteria and they are located in column A and rows 13 and 14.


    Please help!

  • This is a 2 ways Lookup, using Index + Match function


    Try to use this modified formula instead :


    =INDEX(source!$B$4:$GZ$521,MATCH($A15,source!$A$4:$A$521,0),MATCH(1,INDEX((L$14=source!$B$3:$GZ$3)*(L$13=source!$B$2:$GZ$2),0),0))


    Regards