arrange complex excel data from different worksheet and sort into one database

soberguy,

I do it with real worksheet and OK. I did cut formular short because I got 9 worksheets (not only 2).

so the formula i am using is

=IF(ISERROR(INDEX(Sheet1!$A$4:$CJ$200,MATCH($A7,Sheet1!$A$7:$A$200,0)+3,MATCH(M$5,Sheet1!$5:$5,0))),"",(INDEX(Sheet1!$A$4:$CJ$200,MATCH($A7,Sheet1!$A$7:$A$200,0)+3,MATCH(M$5,Sheet1!$5:$5,0)))

and I am also defining table and range name as you suggested. so, in real worksheet, the formula goes like this...


=IF(ISERROR(INDEX(table1,MATCH($A7,position1,0)+3,MATCH(M$5,competency1,0))),"",(INDEX(table1,MATCH($A7,position1,0)+3,MATCH(M$5,competency1,0)))

and I just have to change number of table,position, and competency when I change the department.