• 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
 

p45cal

Well-Known Member
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.
 
Top