Index Match Dragging Problem

[ashraf999]

I am trying to drag the following code across all the worksheet. The mapping is correct and retrieves the values I want. But I need to drag the formula (vertically and horizontally) across the worksheet with minimal manual editing. Basically, Column B contains the employee numbers while Row 6 contains the Project numbers. When I drag the formula, I have to edit the new cell addresses to retrieve the accurate values. Is there an easier way to achieve this objective?

=IF(ISNA(INDEX('Complex Query 4.xlsx'!$AA$2:$AA$1008,MATCH(B8&J6,'Complex Query 4.xlsx'!$E$2:$E$1008&'Complex Query 4.xlsx'!$A$2:$A$1008,0))),"",INDEX('Complex Query 4.xlsx'!$AA$2:$AA$1008,MATCH(B8&J6,'Complex Query 4.xlsx'!$E$2:$E$1008&'Complex Query 4.xlsx'!$A$2:$A$1008,0)))

Thank you

[OnErrorGoto0]

Hi,
You need to use $ signs to fix the B and 6

=IF(ISNA(INDEX('Complex Query 4.xlsx'!$AA$2:$AA$1008,MATCH($B8&J$6,'Complex Query 4.xlsx'!$E$2:$E$1008&'Complex Query 4.xlsx'!$A$2:$A$1008,0))),"",INDEX('Complex Query 4.xlsx'!$AA$2:$AA$1008,MATCH($B8&J$6,'Complex Query 4.xlsx'!$E$2:$E$1008&'Complex Query 4.xlsx'!$A$2:$A$1008,0)))

[ashraf999]

Hi,
You need to use $ signs to fix the B and 6

=IF(ISNA(INDEX('Complex Query 4.xlsx'!$AA$2:$AA$1008,MATCH($B8&J$6,'Complex Query 4.xlsx'!$E$2:$E$1008&'Complex Query 4.xlsx'!$A$2:$A$1008,0))),"",INDEX('Complex Query 4.xlsx'!$AA$2:$AA$1008,MATCH($B8&J$6,'Complex Query 4.xlsx'!$E$2:$E$1008&'Complex Query 4.xlsx'!$A$2:$A$1008,0)))

Thanks a lot. It worked perfectly !

[OnErrorGoto0]

My pleasure.

Please do not forget to mark this thread Solved.