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,
Null answer using MIN in Index Match



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 rangemultiply by sheet  range
2. if this so find minimum of =====
etc etcfor 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 
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, 
Re: Null answer using MIN in Index Match
Two threads concerning the same issue running in parallel. Both now locked until OP contacts a moderator to have one unlocked. The other will then be deleted.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!