IF and LOOKUP with multiple nested IF functions

**ragulduy** · 05-29-2014, 10:29 AM

I think maybe something like:
=LOOKUP(BB9,OFFSET(AG3:AG24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)),OFFSET(AF3:AF24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)))

Edit, just seen your post - if you have the headings 2005/2010/2015 etc above the columns it would be easier to use match on that row to find which column to look at than the curve-fitting approach I used to get the right column.

**hydraulicwave** · 05-29-2014, 10:41 AM

Originally Posted by ragulduy

I think maybe something like:
=LOOKUP(BB9,OFFSET(AG3:AG24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)),OFFSET(AF3:AF24,0,ROUND(0.4*AZ9-800.8-0.4*MOD(AZ9,5)+0.8,0)))

Edit, just seen your post - if you have the headings 2005/2010/2015 etc above the columns it would be easier to use match on that row to find which column to look at than the curve-fitting approach I used to get the right column.

How will that work?
I understand that the match function could find the column, but how do you get it to look at the correct lookup vector and result vector based on that?
thank you