• 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.

Index and match with criteria not working

R

RAM72

Member
I have a working sheet which extract tax code from database column B as lookup and extract tax rate on column E, however when there is a criteria in COLUMN K,then lookup in database corresponding column M and extract the tax amount, if another criteria in column K then look to the corresponding column and extract the tax amount.

Code: 
=SUMIF('DATABASE TAX CODE '!B:B,G2,INDEX('DATABASE TAX CODE '!$B$1:$R$6395,,MATCH(IF($K2="",$AA1,$K2),'DATABASE TAX CODE '!$B$1:$R$1,0)))
 

Attachments

  • WORKINGS .xlsx
    641.6 KB · Views: 5
The problem is in this section
MATCH(IF($K2="",$AA1,$K2),'DATABASE TAX CODE '!$B$1:$R$1,0)
If K2 = "" it will lookup "Duty" in which column ?
I think Workings K2: should be
=IF(ISNUMBER(SEARCH("SADC",I2)),"SADC %","")

That still doesn't help with what to do if K2 is blank, as none of the Headings in DB Tax Code are called "Duty"
 
Hui said:
The problem is in this section
MATCH(IF($K2="",$AA1,$K2),'DATABASE TAX CODE '!$B$1:$R$1,0)
If K2 = "" it will lookup "Duty" in which column ?
I think Workings K2: should be
=IF(ISNUMBER(SEARCH("SADC",I2)),"SADC %","")

That still doesn't help with what to do if K2 is blank, as none of the Headings in DB Tax Code are called "Duty"
Click to expand...


If K2 =SADC on workings sheet, then lookup column M (SADC%) on TAX database and extract corresponding tax otherwise if K2 is blank, then extract on tax database column E general % corresponding tax.

Hope it helps
 
You must log in or register to reply here.
Back
Top