Forum Discussion

rickyricky's avatar
rickyricky
Copper Contributor
Mar 22, 2020

Can't Figure out IF formula for certain Q

I'm trying to align each region with its respective Tax Rate, but the Tax Rate table (Table C) is not in the same order as the Region table (Table A). My current formula for the first box works, but doesn't stay true when pasting down. 

=IF(A4=0,K4,IF(A4=1,L4,IF(A4=2,M4,IF(A4=3,N4,IF(A4=4,O4,IF(A4=5,P4,IF(A4=6,Q4,IF(A4=7,R4,0))))))))

 

(Check Screenshot)

excel
Formulas and Functions

2 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Mar 22, 2020

    rickyricky 

    Please give this a try...

     

    In C4

    =INDEX($K$3:$R$9,MATCH(INDEX($G$4:$G$15,MATCH(B4,$F$4:$F$15,0)),$J$3:$J$9,0),MATCH(A4,$K$2:$R$2,0))

    and then copy it down.

     

    If that doesn't work as desired, please upload a sample workbook (NOT AN IMAGE) to work with.

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 22, 2020

    rickyricky 

    Created a quick-and-dirty "copy" of your schedule (see picture) and entered the formulae required in C4:C13

    =INDEX($K$4:$R$10,MATCH(VLOOKUP(B4,$E$4:$F$15,2,0),$J$4:$J$10,0),MATCH(A4,$K$3:$R$3,0))

     

    If all works well, you should be able to just copy the formula above and paste it in C4 of your own sheet and copy it down.

     

Resources