Results 1 to 3 of 3

Thread: Using MATCH with IF AND <,>=

  1. #1

    Using MATCH with IF AND <,>=



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a table of rates with product types and quantities.
    There are 5 product types and the rate changes for each one by quantity as follows: 0 – 500, 501-1000, 1001-3000, 3000+
    I can use the following formula which works OK for one product type but excel will not allow me to nest enough IF statements to satisfy the number of product types.
    =IF(B2="Type1",IF(AND(C2>0,C2<=500),B10,IF(B2="Type1",IF(AND(C2>500,C2<=1000),C10,……etc
    I believe I can use MATCH but can’t seem to use the <,>= to satisfy the quantities. Can anyone help?

    Attached Files Attached Files

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    In cell C2, try this:

    Code:
    =INDEX($B$10:$E$14,MATCH(A2,$A$10:$A$14,0),MATCH(B2,{0,501,1001,3001},1))
    Copy/Autofill down.

    In cell A13, you have a space " " after the text, which is why the formula isn't finding a result in cell C3.

    Cheers,

  3. #3
    Quote Originally Posted by CheshireCat View Post
    In cell C2, try this:

    Code:
    =INDEX($B$10:$E$14,MATCH(A2,$A$10:$A$14,0),MATCH(B2,{0,501,1001,3001},1))
    Copy/Autofill down.

    In cell A13, you have a space " " after the text, which is why the formula isn't finding a result in cell C3.

    Cheers,
    Thank you so much I never would have worked that out, particularly the last section dealing with the quantities. I need to study Index and Match further. Thanks again. Colin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •