Combing Index, match, small, with multiple criteria

mavs149 · Dec 3, 2013

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 · Dec 10, 2013

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

Origin	Destination	Key	Top carr	Rate
TX	NY	TX-NY	APEX	1.25
OK	NY	OK-NY	Coyote	3

formula: =INDEX('Master data'!A2:A7,MATCH(SMALL(INDEX(('Master data'!D2:D7=RankOutput!C2)*'Master data'!E2:E7+('Master data'!D2:D7<>RankOutput!C2)*10^7,),1),'Master data'!E2:E7,0))

I made this test file so that I could post some data. Honestly, your provided formula works for pulling both the carrier name and the rate. The formula is not working in my actual analysis file for work. It's the same layout just there are around 5000 rows of data. The formula works perfectly in pulling the rates, but for some reason its not pulling the carrier names correctly.

I have tried re-formatting, re-pasting, everything I can think of, it's almost like the column containing the carrier name data is corrupt somewhere.

markmzz · Dec 10, 2013

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

mavs149 said:
Origin
Destination
Key
Top carr
Rate

TX
NY
TX-NY
APEX
1.25

OK
NY
OK-NY
Coyote
3

<tbody>
</tbody>
formula: =INDEX('Master data'!A2:A7,MATCH(SMALL(INDEX(('Master data'!D2:D7=RankOutput!C2)*'Master data'!E2:E7+('Master data'!D2:D7<>RankOutput!C2)*10^7,),1),'Master data'!E2:E7,0))

I made this test file so that I could post some data. Honestly, your provided formula works for pulling both the carrier name and the rate. The formula is not working in my actual analysis file for work. It's the same layout just there are around 5000 rows of data. The formula works perfectly in pulling the rates, but for some reason its not pulling the carrier names correctly.

I have tried re-formatting, re-pasting, everything I can think of, it's almost like the column containing the carrier name data is corrupt somewhere.

Could you post only the formula for the 5000 rows (the formula that you used with your data)?

Markmzz

mavs149 · Dec 10, 2013

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

markmzz said:
Could you post only the formula for the 5000 rows (the formula that you used with your data)?

Markmzz

This is the one that won't pull the carrier names correctly:
=INDEX(P2P!A2:A5943,MATCH(SMALL(INDEX((P2P!D2:D5943='Top P2P'!B2)*P2P!L2:L5943+(P2P!D2:D5943<>'Top P2P'!B2)*10^7,),1),P2P!L2:L5943,0))

And this one works for pulling the rates:
=INDEX(P2P!L2:L5943,MATCH(SMALL(INDEX((P2P!D2:D5943='Top P2P'!B2)*P2P!L2:L5943+(P2P!D2:D5943<>'Top P2P'!B2)*10^7,),1),P2P!L2:L5943,0))

It's very strange, it pulls a carrier name, just the incorrect one. The fact that it works fine in the test file means that it has to have something to do with that column of data. I would think at least.

I appreciate all the help, I'm very new to excel so anything you guys provide is teaching me a lot.

mavs149 · Dec 10, 2013

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

I just noticed that sorting the carrier column A to Z or Z to A causes the carrier names pulled for each lane to change. Still the incorrect ones.

markmzz · Dec 11, 2013

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

mavs149 said:
This is the one that won't pull the carrier names correctly:
=INDEX(P2P!A2:A5943,MATCH(SMALL(INDEX((P2P!D2:D5943='Top P2P'!B2)*P2P!L2:L5943+(P2P!D2:D5943<>'Top P2P'!B2)*10^7,),1),P2P!L2:L5943,0))

And this one works for pulling the rates:
=INDEX(P2P!L2:L5943,MATCH(SMALL(INDEX((P2P!D2:D5943='Top P2P'!B2)*P2P!L2:L5943+(P2P!D2:D5943<>'Top P2P'!B2)*10^7,),1),P2P!L2:L5943,0))

It's very strange, it pulls a carrier name, just the incorrect one. The fact that it works fine in the test file means that it has to have something to do with that column of data. I would think at least.

I appreciate all the help, I'm very new to excel so anything you guys provide is teaching me a lot.

Try the array formulas below and tell me if works:

Code:

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),1),IF(P2P!$D$2:$D$5943='Top P2P'!$B2,P2P!$L$2:$L$5943),0))

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

Layout

Origin	Key	Destination	Top carrier1	Top Rate1	Top carrier2	Top Rate2	Top carrier3	Top Rate3	Top P2P
TX	TX-NY	NY	APEX	1,25	Coyote	1,29	Merit	1,48
OK	OK-NY	NY	Coyote	3,00	APEX	3,60	Merit	5,00
TK	TK-NY1	NY1	Carrier678	1,00	Carrier678	1,00	Carrier678	1,00
TK	TK-NY2	NY2	Carrier103	1,00	Carrier544	1,02	Carrier544	1,02
OK	OK-NY2	NY2	Carrier702	1,02	Carrier702	1,02	Carrier702	1,02
OK	OK-NY1	NY1	Carrier374	1,00	Carrier374	1,00	Carrier314	1,01
********	************	************	************	**********	************	**********	************	**********	********

Markmzz

mavs149 · Dec 11, 2013

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

markmzz said:
Try the array formulas below and tell me if works:

Code:

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),1),IF(P2P!$D$2:$D$5943='Top P2P'!$B2,P2P!$L$2:$L$5943),0)) =SMALL(IF(P2P!$D$2:$D$5943='Top P2P'!$B2,P2P!$L$2:$L$5943),1)

Layout

Origin
Key
Destination
Top carrier1
Top Rate1
Top carrier2
Top Rate2
Top carrier3
Top Rate3
Top P2P

TX
TX-NY
NY
APEX
1,25
Coyote
1,29
Merit
1,48

OK
OK-NY
NY
Coyote
3,00
APEX
3,60
Merit
5,00

TK
TK-NY1
NY1
Carrier678
1,00
Carrier678
1,00
Carrier678
1,00

TK
TK-NY2
NY2
Carrier103
1,00
Carrier544
1,02
Carrier544
1,02

OK
OK-NY2
NY2
Carrier702
1,02
Carrier702
1,02
Carrier702
1,02

OK
OK-NY1
NY1
Carrier374
1,00
Carrier374
1,00
Carrier314
1,01

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

<TBODY>
</TBODY>

Markmzz

So far these two array formulas seem to be working as intended! I'm going to run through it all and spot check it. Thanks for the help, and I'll let you know if I notice anything wrong.

mavs149 · Dec 11, 2013

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

Alright, I've run into one problem. Carriers that have the same rate seem to be the root of the problem. For example, on the first lane, the 1st and 2nd carrier are correct, but the third and fourth carriers both have 1.02 as their rate so depending on if the list is in ascending or descending order it displays the first one twice and skips the second one all together.

So it would list: Apex 1.0 - Coyote 1.01 - Merit 1.02 - Merit 1.02 instead of Merit 1.02 - Command 1.02 like it should.

markmzz · Dec 11, 2013

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

mavs149 said:
Alright, I've run into one problem. Carriers that have the same rate seem to be the root of the problem. For example, on the first lane, the 1st and 2nd carrier are correct, but the third and fourth carriers both have 1.02 as their rate so depending on if the list is in ascending or descending order it displays the first one twice and skips the second one all together.

So it would list: Apex 1.0 - Coyote 1.01 - Merit 1.02 - Merit 1.02 instead of Merit 1.02 - Command 1.02 like it should.

Try this;

Code:

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 · Dec 13, 2013

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

If you have Excel 2010+, you can try this:

Code:

Use only Enter to enter the formulas

=INDEX(P2P!$A$2:$A$5943,MATCH(AGGREGATE(15,6,(P2P!$D$2:$D$5943='Top P2P'!$B2)*
(P2P!$L$2:$L$5943+ROW(P2P!$L$2:$L$5943)/10^7)+(P2P!$D$2:$D$5943<>'Top P2P'!$B2)*10^7,1),
INDEX(P2P!$L$2:$L$5943+ROW(P2P!$L$2:$L$5943)/10^7,),0))

=AGGREGATE(15,6,(P2P!$D$2:$D$5943='Top P2P'!$B2)*P2P!$L$2:$L$5943+(P2P!$D$2:$D$5943<>'Top P2P'!$B2)*10^7,1)

Markmzz

mavs149 · Dec 16, 2013

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

markmzz said:

Try this;

Code:

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

These worked great! Thank you

Combing Index, match, small, with multiple criteria

mavs149

New Member

mavs149

New Member

Excel Facts

markmzz

MrExcel MVP

mavs149

New Member

mavs149

New Member

markmzz

MrExcel MVP

mavs149

New Member

mavs149

New Member

markmzz

MrExcel MVP

markmzz

MrExcel MVP

mavs149

New Member

Similar threads

Forum statistics

Share this page

Combing Index, match, small, with multiple criteria

New Member

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock