If, and, or, index, & match

I am guessing you are talking about the LOOKUP() function and how it is used to determine the column number?

For example in this part of your original formula:

IF($D48=2010,IF($G48="NA",INDEX(Profiles!$B$5:$O$10,MATCH(Data!$E48,Profiles!$B$5:$B$10,0),12),IN DEX(Profiles!$B$15:$O$19,MATCH(Data!$E48,Profiles!$B$15:$B$19,0),12))

After you checked for D48 = 2010, you chech G48 for existance of "NA", you determine which of the 2 INDEX() functions to perform, correct. I.e. =IF(logical_test,value_if_true,value_if_false).

and you continue to do same check for each possible year in D48.

the only difference in each of these checks is that if the year is different, then the column number to do the Index/Match function is different.

e.g. for 2010, you have column 12, for 2011, you have 11, for 2012, you have 10, etc..

so I took advantage of that pattern to shorten the formula and performed a LOOKUP() that looks at the last 2 digits of the value in D48 (I could have also just used the whole 4-digit number).... so in D28 is 2010, the lookup value in the LOOKUP function is "10"... I added +0 to convert it to a real number. Then I look that number up in the first array {10,11,12,13,14}.. when I find it, I extract from the second array at the same position that I found the match... {12,11,10,9,8}... so here the 12 get pulled and that is used in the column Reference of the INDEX() function. All this reduces the number of times you have to repeat the same function over and over, just changing the one argument (column index).

I hope that is better explained.