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]

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.

 

[Dreadknight Nasus]

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.

 

[mavs149]

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.

 

[markmzz]

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

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.

More information about array formula:

Guidelines and examples of array formulas - Excel - Office.com

Array Formulas | Excel Array Formula | Supercharge your formulas

Markmzz

 

[mavs149]

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?

 

[markmzz]

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:

=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

 

[mavs149]

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

Try this:

=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.

 

[mavs149]

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.

 

[markmzz]

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

 

[mavs149]

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

Carrier	Origin	Destination	Key	Rate
Coyote	TX	NY	TX-NY	1.29
Coyote	OK	NY	OK-NY	3
APEX	TX	NY	TX-NY	1.25
APEX	OK	NY	OK-NY	3.6
Merit	TX	NY	TX-NY	1.48
Merit	OK	NY	OK-NY	5

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>