Need formual help for selecting correct column based upon value in another column

thank you so much. the formula worked very well. I knew the index match function and had been trying to use that. I did not know about the Row function, I will study that.

One question, and you probable cannot help, but I thought I would ask.

I entered the formula in AB2. copied it down about 10 rows and all worked well.

I then copied it down to the 52,918 row and my excel locked up. I have tried it twice. Using the formula on the first few rows works fine, but on all rows it locks up excel. The user made one change so this is the final formula.

=IF($AC2>2, SUM($F2:$Q2),IFERROR(INDEX($F:$Q,ROWS($1:2),MATCH($AH$1,$F$1:$Q$1,0)),""))


Would this formula use a large amount of memory? Just wondering if you had run into this before.

That's a lot of rows.

Before giving up on Pete's formula, see if this modification locks up. Also entered in AC2 the ROW($2:$2) part must be the first row in which the formula is entered.

=IF($AC2>2, SUM($F2:$Q2),IFERROR(INDEX($F:$Q,ROW()-ROW($2:$2)+2,MATCH($AH$1,$F$1:$Q$1,0)),""))