So I need a function that will pull back the mins, in order for carrier 1,2,3,4.
The first is simple as I did an index/match to pull back the min of the array of carriers indexed off the carrier names. However, when I wrote out
=IF($BB24>1,INDEX($AT$5:$AZ$5,MATCH(SMALL($AT24:$AZ24,2),$AT24:$AZ24,0)),"-")
For carrier 2 where the second smallest value is what I want returned if gives me the same value. It's strange becase if you use the small function by itself for the 4th line, for example, small(array,1) and small(array,2) both yeild 2 as the return value. When you go to small(array, 3) it bumps up to 3 as it is the next biggest. However, when you put the small function in the index match it keeps returning the same carrier as the previous cell.
Basically I need to be able to identify, all "least transit carriers"......once I have that pulled back I'll look at rates and go with least cost option but I need the service standard identified first.
Please help! w VITR
Transit Least Transit
CNWY FXFE FXNL HMES RDWY DHRN VITR Min Transit # of Min Carrier 1 Carrier 2 Carrier 3 Carrier 4
2 - 3 2 - - 1 1 1 VITR
4 2 3 - 4 - - 2 1 FXFE -
- - 3 2 4 - 1 1 1 VITR -
- 2 3 - 3 - 2 2 2 FXFE
2 2 3 - 4 - - 2 2 CNWY CNWY
- 2 3 - 4 - 2 2 2 FXFE FXFE
3 - 3 - 4 - 2 2 1 VITR -
2 - 3 - 3 - 2 2 2 CNWY CNWY
2 2 3 - 3 - - 2 2 CNWY CNWY
- - 2 2 2 - 1 1 1 VITR -
2 - 3 2 - - 2 2 3 CNWY CNWY
2 2 3 - 3 - - 2 2 CNWY CNWY
- - 3 2 - 2 2 2 3 HMES HMES
- - 2 1 - 1 1 1 3 HMES HMES
1 - - 1 - 1 2 1 3 CNWY CNWY
3 2 3 - 4 - - 2 1 FXFE -
- 2 3 - 3 - 3 2 1 FXFE -
Bookmarks