• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Correction

I am applying following formula and working good but i want to simplify the same

=IF(BPSOld<>0,HLOOKUP(PayOld,IF(BPSNew=4,scale04,IF(BPSNew=5,scale05,IF(BPSNew=6,scale06,IF(BPSNew=7,scale07,scale08)))),1),1)+VLOOKUP(BPSNew,BasicPayScales,4)

I have four different arrays named as Scale04, Scale05 ........... having data from left to right
I want the formula when get data from BPSNew it may dynamically switch to relevant array i.e. Scale04, Scale05 .........

Please help me
 
Instead of the nested IFs you could check out CHOOSE:
CHOOSE(BPSNEW-3,Scale04,Scale05,Scale06,Scale07,Scale8)
or INDEX and MATCH.
Best to attach a workbook (or a mock-up workbook if the data's sensitive).
Also important is which version of Excel you're using because there are some relatively new(ish) functions which could make things easy.
 
Back
Top