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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Carrier
Origin
Destination
Key
Rate
1
TX
NY
TX-NY
1.29
1
OK
NY
OK-NY
3
2
TX
NY
TX-NY
1.25
2
OK
NY
OK-NY
3.6
3
TX
NY
TX-NY
1.48
3
OK
NY
OK-NY
1.7

<TBODY>
</TBODY>

Origin</SPAN>
Destination</SPAN>
Key</SPAN>
Top Carrier</SPAN>
TX</SPAN>
NY</SPAN>
TX-NY</SPAN>
2</SPAN>
OK </SPAN>
NY</SPAN>
OK-NY</SPAN>

<TBODY>
</TBODY>

<TBODY>
</TBODY>
 
Upvote 0
=INDEX(P2P!A:A,MATCH(SMALL(P2P!L:L,1),P2P!L:L,0))

This formula is pulling "2" because that carrier has the overall lowest rate. But I want the formula to also check the key so that it returns lowest rate for TX-NY overall and OK-NY overall
 
Upvote 0
Try this:
Code:
=INDEX(P2P!A:A,MATCH(MIN(IF(P2P!D:D=I2,P2P!L:L,1000)),P2P!L:L,0))

This code assumes your carrier is in column A, your Key is in column D, and your rate is in column L. Also, the "I2" is referring to the cell that contains the key you're matching to, TX-NY or OK-NY, so you can copy the formula down.
 
Upvote 0
Also, if you have to edit or manually enter this formula, you have to enter it with ctrl+shift+enter, as opposed to just 'enter', because it's an array formula.
 
Upvote 0
Try this:
Code:
=INDEX(P2P!A:A,MATCH(MIN(IF(P2P!D:D=I2,P2P!L:L,1000)),P2P!L:L,0))

This code assumes your carrier is in column A, your Key is in column D, and your rate is in column L. Also, the "I2" is referring to the cell that contains the key you're matching to, TX-NY or OK-NY, so you can copy the formula down.

Can the small function be used in this situation? Because I also want to return the 2nd, 3rd, fourth smallest as well in the main file in a separate column.

i appreciate the help.
 
Upvote 0
Try this small modification of the Dreadknight's formula:

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

=INDEX(P2P!A:A,MATCH(SMALL(IF(P2P!D:D=I2,P2P!L:L),1),P2P!L:L,0))

Markmzz
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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