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:

Origin

Key

Destination

Top carrier1

Top Rate1

Top carrier2

Top Rate2

Top carrier3

Top Rate3

Top P2P-P

P2P!$L$2:$L$7

TX

TX-NY

NY

APEX

1,25

Coyote

1,29

Merit

1,29

{1,29;3;1,25;3,6;1,29;5}

OK

OK-NY

NY

Coyote

3,00

APEX

3,60

Merit

5,00

see the numbers 1,29 and 1,29

******

******

******

******

******

******

******

******

******

******

ROW(P2P!$L$2:$L$7)/10^7

*

{0,0000002;0,0000003;0,0000004;0,0000005;0,0000006;0,0000007}

Carrier

Origin

Destination

Key

Rate

P2P-P

Coyote

TX

NY

TX-NY

1,29

IF(P2P!$D$2:$D$7='Top P2P-P'!$B2,P2P!$L$2:$L$7+ROW(P2P!$L$2:$L$7)/10^7)

Coyote

OK

NY

OK-NY

3,00

{1,2900002;FALSO;1,2500004;FALSO;1,2900006;FALSO;FALSO;FALSO}

APEX

TX

NY

TX-NY

1,25

Now, see the numbers 1,2900002 and 1,2900006 (the same 1,29 and 1,29 in the first formula)

APEX

OK

NY

OK-NY

3,60

Merit

TX

NY

TX-NY

1,29

Merit

OK

NY

OK-NY

5,00

*******

******

************

******

**

**

**

**

**

**

**

*******

*******

**

******************************************************************************************

<tbody>
</tbody>

I hope that the table above helps.


Markmzz