How to return value from another column after using Min / IF calculation

Hi All,

I have a formula where I wish to locate the minimum or cheapest rate available to us per below:

=MIN(IF(Rates!$A$6:$A$145=Calculator!$D$6,IF(Rates!$D$4:$S$4=Calculator!$D$12,IF(Rates!$D$5:$S$5=Calculator!$D$14,Rates!$D$6:$S$145))))

This works fine and returns the value I require, however in another cell I also need it to tell me the carrier which is in column C of the same 'Rates' Sheet that corresponds with this lowest or cheapest value.


Thanks,
Gaz

Hi, unfortunately I can't seem to get this to work.

My result is in cell D24 and so I have the below adapted from your response:

=INDEX(Rates!$C$6:$C$145,SUMPRODUCT(Rates!$D$6:$S$145=Calculator!$D$24)*(ROW(Rates!$D$6:$D$145)))-ROW(Rates!$D$6)+1

Thanks,
Gaz

Hi, hopefully this might help! Cell concerned is highlighted in yellow (D22)

Thanks
Gaz

Hi, I've cleared the errors and entered the formula which is returning a result, however it appears to constantly be carrier A no matter what the other parameters which incidentally is the value in C6?


Thanks,
Gaz

Thanks, although I guess I am also getting further problems because not all the values are unique and so it is returning #REF! results on some lanes?

Regards,
Gaz

Hi there, did you have any luck with this?

Thanks,
Gaz

Assuming

a) you're on O365 {per profile}
b) you've removed the #VALUE! errors
c) you want to return all Carriers that meet the criterion {Destination, Region, Size & Cheapest Rate}

Formula:

D22:

=TEXTJOIN(";",TRUE,INDEX(REPT(Rates!$C$6:$C$145,SIGN((Rates!$A$6:$A$145=$D$6)*(Rates!$D$4:$S$4=Calculator!$D$12)*(Rates!$D$5:$S$5=Calculator!$D$14)*(Rates!$D$6:$S$145=Calculator!$D$24))),0))

above would return A based on the earlier sample, but if you were to copy Rates!K14 to K16 you would then get A;C

however, I suspect the above is over-engineered for what you really need...