formula help... lookup or ref?

[MGriff]

I need help creating a formula. See below example... what I need is to identify the carrier with lowest rate.

Origin Dest Carrier 1 Carrier 2 BEST 20' BEST 40' RATE
20' 40' 20' 40' rate carrier rate carrier

MCI LAX 1000 1400 800 1200 $800.00 ??? $1,200.00 ???

 

[Don Guillett]

I need help creating a formula. See below example... what I need is to identify the carrier with lowest rate.



Origin Dest Carrier 1 Carrier 2 BEST 20' BEST 40' RATE

20' 40' 20' 40' rate carrier rate carrier



MCI LAX 1000 1400 800 1200 $800.00 ??? $1,200.00 ???

provide a file

 

[Ron Rosenfeld]

I need help creating a formula. See below example... what I need is to identify the carrier with lowest rate.

Origin Dest Carrier 1 Carrier 2 BEST 20' BEST 40' RATE
20' 40' 20' 40' rate carrier rate carrier

MCI LAX 1000 1400 800 1200 $800.00 ??? $1,200.00 ???

You can probably use a combination of INDEX & MATCH. For that ot work effectively, you might need to make some changes in your data table format. If that is not possible, perhaps a VBA solution if allowed.

Something like:
=INDEX($C$1:$F$1,1,MATCH(G4,C$4:F$4,0))
entered as an ARRAY formula, and where G4 contains the best 20' rate.

I am, of course, guessing at where your data table is, and how things are set up. So post back with your questions.