Combing Index, match, small, with multiple criteria

mavs149

New Member
Joined
Sep 13, 2013
Messages
23
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.
 
Actually I think that array formulas only need to be entered with ctrl+shift+enter when they're being entered into multiple cells as opposed to one, but I'm not positive. I'm just used to using ctrl+shift+enter whenever I deal with arrays, just to be safe.
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Actually I think that array formulas only need to be entered with ctrl+shift+enter when they're being entered into multiple cells as opposed to one, but I'm not positive. I'm just used to using ctrl+shift+enter whenever I deal with arrays, just to be safe.

Just kidding. The formula actually doesn't work unless you use ctrl+shift+enter, I just tested it.
 
Upvote 0
Re: (SOLVED) Combing Index, match, small, with multiple criteria

Just kidding. The formula actually doesn't work unless you use ctrl+shift+enter, I just tested it.

You're correct. It does require the array. Thanks for all the help guys, my problem is now solved.
 
Upvote 0
Upvote 0
Re: (SOLVED) Combing Index, match, small, with multiple criteria

Sorry to reopen this thread, but I'm wondering if there is any way to make a working non-array version of this formula. Or is it not possible to combine index, match, if, small together without using an array?
 
Upvote 0
Re: (SOLVED) Combing Index, match, small, with multiple criteria

Sorry to reopen this thread, but I'm wondering if there is any way to make a working non-array version of this formula. Or is it not possible to combine index, match, if, small together without using an array?

Try this:

Code:
=INDEX(P2P!$A$2:$A$7,MATCH(SMALL(INDEX((P2P!$D$2:$D$7=$R2)*P2P!$L$2:$L$7+(P2P!$D$2:$D$7<>$R2)*10^7,),1),P2P!$L$2:$L$7,0))

PS: don't use entire column.

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

Try this:

Code:
=INDEX(P2P!$A$2:$A$7,MATCH(SMALL(INDEX((P2P!$D$2:$D$7=$R2)*P2P!$L$2:$L$7+(P2P!$D$2:$D$7<>$R2)*10^7,),1),P2P!$L$2:$L$7,0))

PS: don't use entire column.

Markmzz

It's so strange, this formula works fine with the rates, but when I use it to pull the carrier's name most of them come out wrong. I've tried checking the carrier name column for formatting issues etc to no avail.
 
Upvote 0
Re: (SOLVED) Combing Index, match, small, with multiple criteria

When setting the small function to 2..3..4 etc it pulls the correct carrier name. Just not for 1. I wonder if the carrier array has bad data in it somewhere. Just doesn't make sense to me why it wouldn't work.
 
Upvote 0
Re: (SOLVED) Combing Index, match, small, with multiple criteria

It's so strange, this formula works fine with the rates, but when I use it to pull the carrier's name most of them come out wrong. I've tried checking the carrier name column for formatting issues etc to no avail.

When setting the small function to 2..3..4 etc it pulls the correct carrier name. Just not for 1. I wonder if the carrier array has bad data in it somewhere. Just doesn't make sense to me why it wouldn't work.

Could you post some data and the formula?

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

[TABLE="width: 335"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Carrier[/TD]
[TD]Origin[/TD]
[TD]Destination[/TD]
[TD]Key[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]Coyote[/TD]
[TD]TX[/TD]
[TD]NY[/TD]
[TD]TX-NY[/TD]
[TD="align: right"]1.29[/TD]
[/TR]
[TR]
[TD]Coyote[/TD]
[TD]OK[/TD]
[TD]NY[/TD]
[TD]OK-NY[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]APEX[/TD]
[TD]TX[/TD]
[TD]NY[/TD]
[TD]TX-NY[/TD]
[TD="align: right"]1.25[/TD]
[/TR]
[TR]
[TD]APEX[/TD]
[TD]OK[/TD]
[TD]NY[/TD]
[TD]OK-NY[/TD]
[TD="align: right"]3.6[/TD]
[/TR]
[TR]
[TD]Merit[/TD]
[TD]TX[/TD]
[TD]NY[/TD]
[TD]TX-NY[/TD]
[TD="align: right"]1.48[/TD]
[/TR]
[TR]
[TD]Merit[/TD]
[TD]OK[/TD]
[TD]NY[/TD]
[TD]OK-NY[/TD]
[TD="align: right"]5
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top