I hope when I am explaining you this ..
You know something about INDEX function...
INDEX - returns the cell value at the intersection of row and column in the table array provided...
what I did is...
first I match the $C$7 with the 'A2'!$A$2 for your Govt. and Non Govt Clause..
then If It matches then INDEX formula come into action..
my INDEX function took 3 argument..
first is the tableArray... ie the whole table in the other sheets..
second is RowNo.. ie the row in the table array you want to refer..
third one is ColumnNo. ie the column in the table array you want to refer..
the reason behind I use $A1 for rows and A$1 for columns is that..
when I drag the formula vertically, it must give value in the next row but in the same column(that is why $ is used before A) in the Index provided..
and similiarly...
when I drag the formula horizontally, it must give value in the next column but in the same row(that is why $ is used before 1) in the Index provided..
I hope you have understood this..
Don't forget to click *
Bookmarks