Lowest Cost Lookup

[rickyken]

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!!

Postcode Carrier Rate Lowest Cost cw10 carrier 1 180 Carrier Rate cw10 carrier 2 187 cw10 XXX YYY cw10 carrier 3 188 cw8 XXX YYY cw8 carrier 2 180 st4 XXX YYY cw8 carrier 1 181 cw8 carrier 3 190 st4 carrier 3 270 st4 carrier 1 250 st4 carrier 2 255 <colgroup><col><col><col span="4"><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[AhoyNC]

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
	A	B	C	D	E	F	G	H
1	Postcode	Carrier	Rate			Lowest Cost
2	cw10	carrier 1	180				Carrier	Rate
3	cw10	carrier 2	187			cw10	carrier 1	180
4	cw10	carrier 3	188			cw8	carrier 2	180
5	cw8	carrier 2	180			st4	carrier 1	250
6	cw8	carrier 1	181
7	cw8	carrier 3	190
8	st4	carrier 3	270
9	st4	carrier 1	250
10	st4	carrier 2	255
Sheet

 

[Yongle]

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

Postcode	Carrier	Rate
cw10	carrier 1	180
cw10	carrier 2	187
cw10	carrier 3	188
cw8	carrier 2	180
cw8	carrier 1	181
cw8	carrier 3	190
st4	carrier 1	250
st4	carrier 2	255
st4	carrier 3	270

<tbody>
</tbody>

RESULT

	Carrier	Rate
cw10	carrier 1	180
cw8	carrier 2	180
st4	carrier 1	250

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Postcode	Carrier	Rate			Lowest Cost
2​	cw10	carrier 1	180​				Rate	Carrier
3​	cw10	carrier 2	187​			cw10	180​	carrier 1	carrier 3
4​	cw10	carrier 3	180​			cw8	180​	carrier 2
5​	cw8	carrier 2	180​			st4	250​	carrier 1
6​	cw8	carrier 1	181​
7​	cw8	carrier 3	190​
8​	st4	carrier 3	270​
9​	st4	carrier 1	250​
10​	st4	carrier 2	255​

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))),"")