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?

Please Login or Register  to view this content.

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.