Null answer using MIN in Index Match

    Hello,

    Cell D4 in my Discounts sheet attached, utilises an Index Match formula that creates an array based on rows that match on three variables(columns), then uses MIN to select a value. It also contains a nested IF. I kep on getting "" as an answer. I've fiddled around with the formula but nothing is working.

    Can anyone kindly diagnose the problem with this?

    IF(MIN(IF((Rates!$D$4:$D$135='Regulated Tariffs'!$A4)*(Rates!$E$4:$E$135='Regulated Tariffs'!$B4)*(Rates!$F$4:$F$135='Regulated Tariffs'!$C4),ROW(Rates!$A$4:$A$135),"")),INDEX(Rates!N:N,MIN(IF((Rates!$D$4:$D$135='Regulated Tariffs'!$A4)*(Rates!$E$4:$E$135='Regulated Tariffs'!$B4)*(Rates!$F$4:$F$135='Regulated Tariffs'!$C4),ROW(Rates!$A$4:$A$135),""))),"")


    Thanks,

    Re: Null answer using MIN in Index Match


    confusing to me
    it would be helpful if you structure your requirement step by step with reference to the cell addresses
    1. condition 1 is sheet ----range-----= sheet----- rates range--------multiply by sheet ------ range-----
    2. if this so find minimum of =====
    etc etc



    for e.g. rates sheet D4 and D5 are strings. you equal this to regulated tariff sheet A4which is a string multiplied by rates!E4 which is a string.how can you multiple string by string
    greetings. perhaps I am seeing wrongly

    I am not an expert. better solutions may be available. $$$venkat$$$1926@$$$gmail.com

    Re: Null answer using MIN in Index Match


    Hi Venkat,

    I think I have worked out why it wasn't working. The references were not quite right.

    Thank you for your attention.

    Another problem that I think anyone using those types of array formulas need to be wary of, avoid error messages in the cells that you are including in the Multiplication check. Zero's or Null is fine. Just not errors.

    regards,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login