# 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.

• 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

• Thank you - that's awesome. Worked perfectly. Much appreciated.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!