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

[Gazsim]

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

[ChemistB]

With your result for the minimum in B2, try

Please Login or Register  to view this content.

This assumes that only one minimum rate exists. You might want to check that with an IF, something like

Please Login or Register  to view this content.

[Gazsim]

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

[ChemistB]

Hard to say without an uploaded example but try

Please Login or Register  to view this content.

[Gazsim]

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

Thanks
Gaz

[ChemistB]

First issue is you have some #Value errors in your table. Clear those out.
To find them try F5> Special > Constants> Errors> Find next

Then the formula

Please Login or Register  to view this content.

in D6 works

[Gazsim]

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

[ChemistB]

Sorry, here’s the formula for D22


Formula: copy to clipboard
=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)))

See attached.

[Gazsim]

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

[ChemistB]

Yes, my approach will only work with unique values. I'll think on it.

[Gazsim]

Hi there, did you have any luck with this?

Thanks,
Gaz

[XLent]

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}

Please Login or Register  to view this content.

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