Lowest Cost Lookup

R

rickyken

New Member
Joined
Apr 26, 2018
Messages
2
Hi, I have a list of postcodes in column A, carriers in column B, and their rates in column C as below. I'm looking for a for a formula to replace "XXX" under the lowest cost section to look up the postcode in the list in column A and return the applicable carrier from column B based on which carriers rate is lowest in column C. I've tried mixing the INDEX, MATCH and MIN functions but can't get it to work correctly. Does anyone know if and how this can be achieved? Once I've got "XXX" sorted, I'll be able to use the same method for "YYY" to show the lowest rate. Cheers for your help!!
[TABLE="width: 558"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 558"]
<colgroup><col><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD]Postcode[/TD]
[TD]Carrier[/TD]
[TD]Rate[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Lowest Cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cw10[/TD]
[TD]carrier 1[/TD]
[TD="align: right"]180[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Carrier[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]cw10[/TD]
[TD]carrier 2[/TD]
[TD="align: right"]187[/TD]
[TD][/TD]
[TD][/TD]
[TD]cw10[/TD]
[TD]XXX[/TD]
[TD]YYY[/TD]
[/TR]
[TR]
[TD]cw10[/TD]
[TD]carrier 3[/TD]
[TD="align: right"]188[/TD]
[TD][/TD]
[TD][/TD]
[TD]cw8[/TD]
[TD]XXX[/TD]
[TD]YYY
[/TD]
[/TR]
[TR]
[TD]cw8[/TD]
[TD]carrier 2[/TD]
[TD="align: right"]180[/TD]
[TD][/TD]
[TD][/TD]
[TD]st4[/TD]
[TD]XXX[/TD]
[TD]YYY[/TD]
[/TR]
[TR]
[TD]cw8[/TD]
[TD]carrier 1[/TD]
[TD="align: right"]181[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cw8[/TD]
[TD]carrier 3[/TD]
[TD="align: right"]190[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]st4[/TD]
[TD]carrier 3[/TD]
[TD="align: right"]270[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]st4[/TD]
[TD]carrier 1[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]st4[/TD]
[TD]carrier 2[/TD]
[TD="align: right"]255[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Here's one way.
The INDEX formula in G3 is an array formula and must be entered with CTRL-SHIFT-ENETR.
Drag formulas down as needed.
Excel Workbook
ABCDEFGH
1PostcodeCarrierRateLowest Cost
2cw10carrier 1180CarrierRate
3cw10carrier 2187cw10carrier 1180
4cw10carrier 3188cw8carrier 2180
5cw8carrier 2180st4carrier 1250
6cw8carrier 1181
7cw8carrier 3190
8st4carrier 3270
9st4carrier 1250
10st4carrier 2255
Sheet
 
Upvote 0
If you want a smaller formula, sort the data first and the first match in each postcode is the cheapest

Postcodes in column A, Carriers in Column B, Rate in Column C
PostCode to lookup in F2

=INDEX(B:B,MATCH(F2,A:A,0)) Cheapest Carrier
=INDEX(C:C,MATCH(F2,A:A,0)) Rate

SORT
First Level by Postcode
Second level by cost (from lowest to highest)
Looks like this

<tbody>
[TD="class: xl65"]Postcode[/TD]
[TD="class: xl65"]Carrier[/TD]
[TD="class: xl65"]Rate[/TD]

[TD="class: xl65, width: 64"]cw10[/TD]
[TD="class: xl65, width: 64"]carrier 1[/TD]
[TD="class: xl65, width: 64"]180[/TD]

[TD="class: xl65, width: 64"]cw10[/TD]
[TD="class: xl65, width: 64"]carrier 2[/TD]
[TD="class: xl65, width: 64"]187[/TD]

[TD="class: xl65, width: 64"]cw10[/TD]
[TD="class: xl65, width: 64"]carrier 3[/TD]
[TD="class: xl65, width: 64"]188[/TD]

[TD="class: xl65, width: 64"]cw8[/TD]
[TD="class: xl65, width: 64"]carrier 2[/TD]
[TD="class: xl65, width: 64"]180[/TD]

[TD="class: xl65, width: 64"]cw8[/TD]
[TD="class: xl65, width: 64"]carrier 1[/TD]
[TD="class: xl65, width: 64"]181[/TD]

[TD="class: xl65, width: 64"]cw8[/TD]
[TD="class: xl65, width: 64"]carrier 3[/TD]
[TD="class: xl65, width: 64"]190[/TD]

[TD="class: xl65, width: 64"]st4[/TD]
[TD="class: xl65, width: 64"]carrier 1[/TD]
[TD="class: xl65, width: 64"]250[/TD]

[TD="class: xl65, width: 64"]st4[/TD]
[TD="class: xl65, width: 64"]carrier 2[/TD]
[TD="class: xl65, width: 64"]255[/TD]

[TD="class: xl65, width: 64"]st4[/TD]
[TD="class: xl65, width: 64"]carrier 3[/TD]
[TD="class: xl65, width: 64"]270[/TD]

</tbody>

RESULT
[TABLE="width: 192"]
<tbody>[TR]
[TD][/TD]
[TD]Carrier[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]cw10[/TD]
[TD]carrier 1[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]cw8[/TD]
[TD]carrier 2[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]st4[/TD]
[TD]carrier 1[/TD]
[TD]250[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][/tr][tr][td]
1​
[/td][td]Postcode[/td][td]Carrier[/td][td]Rate[/td][td][/td][td][/td][td]Lowest Cost[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
2​
[/td][td]cw10[/td][td]carrier 1[/td][td]
180​
[/td][td][/td][td][/td][td][/td][td]Rate[/td][td]Carrier[/td][td][/td][/tr]
[tr][td]
3​
[/td][td]cw10[/td][td]carrier 2[/td][td]
187​
[/td][td][/td][td][/td][td]cw10[/td][td]
180​
[/td][td]carrier 1[/td][td]carrier 3[/td][/tr]
[tr][td]
4​
[/td][td]cw10[/td][td]carrier 3[/td][td]
180​
[/td][td][/td][td][/td][td]cw8[/td][td]
180​
[/td][td]carrier 2[/td][td][/td][/tr]
[tr][td]
5​
[/td][td]cw8[/td][td]carrier 2[/td][td]
180​
[/td][td][/td][td][/td][td]st4[/td][td]
250​
[/td][td]carrier 1[/td][td][/td][/tr]
[tr][td]
6​
[/td][td]cw8[/td][td]carrier 1[/td][td]
181​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td]cw8[/td][td]carrier 3[/td][td]
190​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td]st4[/td][td]carrier 3[/td][td]
270​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td]st4[/td][td]carrier 1[/td][td]
250​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td]st4[/td][td]carrier 2[/td][td]
255​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$F3,IF($C$2:$C$10=$G3,ROW($B$2:$B$10)-ROW($B$2)+1)),COLUMNS($H3:H3))),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
281
Cquake
C
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
123
Fluff
Fluff
WESTERNWALL
  • Question Question
Sum and Count functions
Replies
4
Views
238
WESTERNWALL
WESTERNWALL
S
  • Question Question
Easier Array to arrange columns VBA/Macro
Replies
4
Views
3K
Akuini
Akuini
A
  • Question Question
Match/Lookup separate tables into one column
2
Replies
12
Views
1K
Skybluekid
Skybluekid

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

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