Help with Offset/Match Formula

Hello. I have the formula below and I can't figure it out. It refers to a tab called CIP. F147 does= "priorCIPavg", so I just need to evaluate the true part of the IF formula. I would like the formula to return the value from cell AW435 in the attached sheet (all data has been deleted). Currently it is returning 0 I would appreciate any help. Also, if you could explain what is going on, I would appreciate it as well!!


=IF(F147="priorCIPavg",OFFSET(CIP!$AQ$393,MATCH(newPnLdate,CIP!$D$394:$D$451,0)-1,0),IF(F147="currAP+11",(((I80+I152)*1000)+paidhistdollars)/((H80*1000)+paidhistvol),IF(F147="Accrued",(((I80+I152)*1000)+accrhistdollars)/((H80*1000)+accrhistvol),lastmohistdollars/lastmohistvol)))

We would love to continue to help you with your query, but first, before we can proceed, please see Forum Rule #1 about proper thread titles and adjust accordingly...

• To change a Title on your post, click EDIT then Go Advanced.
• If 2 days have passed, ask a moderator to do it for you.

From what I can make out, you are refencing ranges called "newPnLdate" - MATCH(newPnLdate,CIP!$D$394:$D$451,0) - and "currAP+11" that do not exist (there may be others)

1st, chect to make sure all the ranges you are referencing exist. Also, if "currAP+11" and "Accrued" are refering to actual ranges, they will not work if you put them within ""...doing so is telling excel that is is a word in a cell - inthe 1st instance, exactly that - a cell that contains currAP+11