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.

 

[mavs149]

Re: (SOLVED) Combing Index, match, small, with multiple criteria

Try this;

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

=INDEX(P2P!$A$2:$A$5943,MATCH(SMALL(IF(P2P!$D$2:$D$5943='Top P2P'!$B2,P2P!$L$2:$L$5943+ROW(P2P!$L$2:$L$5943)/10^7),1),
IF(P2P!$D$2:$D$5943='Top P2P'!$B2,P2P!$L$2:$L$5943+ROW(P2P!$L$2:$L$5943)/10^7),0))

=SMALL(IF(P2P!$D$2:$D$5943='Top P2P'!$B2,P2P!$L$2:$L$5943),1)

Markmzz

Markmzz,

I'm trying to break this formula down so I understand it better for in the future. What exactly are you doing with the +ROW function and the /10^7?

Thanks,

 

[markmzz]

Re: (SOLVED) Combing Index, match, small, with multiple criteria

Markmzz,

I'm trying to break this formula down so I understand it better for in the future. What exactly are you doing with the +ROW function and the /10^7?

Thanks,

Look at this:

[TABLE="width: 792"]
<tbody>[TR]
[TD="class: xl70, width: 42, bgcolor: transparent"]Origin
[/TD]
[TD="class: xl70, width: 37, bgcolor: transparent"]Key
[/TD]
[TD="class: xl70, width: 67, bgcolor: transparent"]Destination
[/TD]
[TD="class: xl70, width: 45, bgcolor: transparent"]Top carrier1
[/TD]
[TD="class: xl70, width: 37, bgcolor: transparent"]Top Rate1
[/TD]
[TD="class: xl70, width: 45, bgcolor: transparent"]Top carrier2
[/TD]
[TD="class: xl70, width: 37, bgcolor: transparent"]Top Rate2
[/TD]
[TD="class: xl70, width: 45, bgcolor: transparent"]Top carrier3
[/TD]
[TD="class: xl70, width: 54, bgcolor: transparent"]Top Rate3
[/TD]
[TD="class: xl71, width: 53, bgcolor: transparent"]Top P2P-P
[/TD]
[TD="class: xl65, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl69, width: 471, bgcolor: transparent"]P2P!$L$2:$L$7
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TX
[/TD]
[TD="class: xl65, bgcolor: transparent"]TX-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl67, bgcolor: yellow"]APEX
[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]1,25
[/TD]
[TD="class: xl67, bgcolor: yellow"]Coyote
[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]1,29
[/TD]
[TD="class: xl67, bgcolor: yellow"]Merit
[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]1,29
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]{1,29;3;1,25;3,6;1,29;5}
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]OK
[/TD]
[TD="class: xl65, bgcolor: transparent"]OK-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl67, bgcolor: yellow"]Coyote
[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]3,00
[/TD]
[TD="class: xl67, bgcolor: yellow"]APEX
[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]3,60
[/TD]
[TD="class: xl67, bgcolor: yellow"]Merit
[/TD]
[TD="class: xl68, bgcolor: yellow, align: right"]5,00
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]see the numbers 1,29 and 1,29
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]ROW(P2P!$L$2:$L$7)/10^7
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]{0,0000002;0,0000003;0,0000004;0,0000005;0,0000006;0,0000007}
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Carrier
[/TD]
[TD="class: xl65, bgcolor: transparent"]Origin
[/TD]
[TD="class: xl65, bgcolor: transparent"]Destination
[/TD]
[TD="class: xl65, bgcolor: transparent"]Key
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Rate
[/TD]
[TD="class: xl69, bgcolor: transparent"]P2P-P
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Coyote
[/TD]
[TD="class: xl65, bgcolor: transparent"]TX
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]TX-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1,29
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]IF(P2P!$D$2:$D$7='Top P2P-P'!$B2,P2P!$L$2:$L$7+ROW(P2P!$L$2:$L$7)/10^7)
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Coyote
[/TD]
[TD="class: xl65, bgcolor: transparent"]OK
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]OK-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3,00
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]{1,2900002;FALSO;1,2500004;FALSO;1,2900006;FALSO;FALSO;FALSO}
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]APEX
[/TD]
[TD="class: xl65, bgcolor: transparent"]TX
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]TX-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1,25
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]Now, see the numbers 1,2900002 and 1,2900006 (the same 1,29 and 1,29 in the first formula)
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]APEX
[/TD]
[TD="class: xl65, bgcolor: transparent"]OK
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]OK-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3,60
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Merit
[/TD]
[TD="class: xl65, bgcolor: transparent"]TX
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]TX-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1,29
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Merit
[/TD]
[TD="class: xl65, bgcolor: transparent"]OK
[/TD]
[TD="class: xl65, bgcolor: transparent"]NY
[/TD]
[TD="class: xl65, bgcolor: transparent"]OK-NY
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5,00
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*******
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]************
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]*******
[/TD]
[TD="class: xl65, bgcolor: transparent"]*******
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]******************************************************************************************
[/TD]
[/TR]
</tbody>[/TABLE]



I hope that the table above helps.


Markmzz