Combing Index, match, small, with multiple criteria

[mavs149]

Hello all,

This formula works: =INDEX(P2P!A:A,MATCH(SMALL(P2P!L:L,1),P2P!L:L,0))

What it's doing is pulling the carrier name that has the lowest rate in the master data table. The only problem is there are multiple lanes so it's only giving me the lowest rate out of all of them.

I created a identifier column in the master data table and the sheet that has the output. I need to figure out how to give match more criteria so that it also checks the identifier so that it will return the lowest rate in that specific lane.

I'm new to excel, and any advice is greatly appreciated.

 

[Dreadknight Nasus]

Post an example?

 

[mavs149]

Not sure how to attach a sample file..

 

[mavs149]

[TABLE="width: 251"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]
[TABLE="width: 251"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Carrier
[/TD]
[TD="width: 64, bgcolor: transparent"]Origin
[/TD]
[TD="width: 79, bgcolor: transparent"]Destination
[/TD]
[TD="width: 64, bgcolor: transparent"]Key
[/TD]
[TD="width: 64, bgcolor: transparent"]Rate
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]TX
[/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]TX-NY
[/TD]
[TD="bgcolor: transparent, align: right"]1.29
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]OK
[/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]OK-NY
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"]TX
[/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]TX-NY
[/TD]
[TD="bgcolor: transparent, align: right"]1.25
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"]OK
[/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]OK-NY
[/TD]
[TD="bgcolor: transparent, align: right"]3.6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]TX
[/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]TX-NY
[/TD]
[TD="bgcolor: transparent, align: right"]1.48
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]OK
[/TD]
[TD="bgcolor: transparent"]NY
[/TD]
[TD="bgcolor: transparent"]OK-NY
[/TD]
[TD="bgcolor: transparent, align: right"]1.7
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 281"]
<TBODY>[TR]
[TD]Origin</SPAN>
[/TD]
[TD]Destination</SPAN>
[/TD]
[TD]Key</SPAN>
[/TD]
[TD]Top Carrier</SPAN>
[/TD]
[/TR]
[TR]
[TD]TX</SPAN>
[/TD]
[TD]NY</SPAN>
[/TD]
[TD]TX-NY</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]OK </SPAN>
[/TD]
[TD]NY</SPAN>
[/TD]
[TD]OK-NY</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[mavs149]

=INDEX(P2P!A:A,MATCH(SMALL(P2P!L:L,1),P2P!L:L,0))

This formula is pulling "2" because that carrier has the overall lowest rate. But I want the formula to also check the key so that it returns lowest rate for TX-NY overall and OK-NY overall

 

[Dreadknight Nasus]

Try this:

=INDEX(P2P!A:A,MATCH(MIN(IF(P2P!D:D=I2,P2P!L:L,1000)),P2P!L:L,0))

This code assumes your carrier is in column A, your Key is in column D, and your rate is in column L. Also, the "I2" is referring to the cell that contains the key you're matching to, TX-NY or OK-NY, so you can copy the formula down.

 

[Dreadknight Nasus]

Also, if you have to edit or manually enter this formula, you have to enter it with ctrl+shift+enter, as opposed to just 'enter', because it's an array formula.

 

[mavs149]

Try this:

=INDEX(P2P!A:A,MATCH(MIN(IF(P2P!D:D=I2,P2P!L:L,1000)),P2P!L:L,0))

This code assumes your carrier is in column A, your Key is in column D, and your rate is in column L. Also, the "I2" is referring to the cell that contains the key you're matching to, TX-NY or OK-NY, so you can copy the formula down.

Can the small function be used in this situation? Because I also want to return the 2nd, 3rd, fourth smallest as well in the main file in a separate column.

i appreciate the help.

 

[markmzz]

Try this small modification of the Dreadknight's formula:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=INDEX(P2P!A:A,MATCH(SMALL(IF(P2P!D:D=I2,P2P!L:L),1),P2P!L:L,0))

Markmzz

 

[mavs149]

These worked! I appreciate the help. I did have one more question though, why does this formula in particular have to use "array"? I noticed that both array and non-array work in this situation.