Lookup multiple values, find minimum amount and return another value

[thevidablue]

Hi
I have a tricky formula I cannot figure out myself. Tried various combination with no real luck. Simply over my head

First off, my "masterdata" in one tab looks like below:

[TABLE="width: 399"]
<tbody>[TR]
[TD]TRADE
[/TD]
[TD]POL
[/TD]
[TD]Carrier
[/TD]
[TD="align: right"]Value
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEGOT
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]700
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEHEL
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SENRK
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]850
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEGOT
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]750
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEHEL
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]900
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SENRK
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]700
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEGOT
[/TD]
[TD]NYKU
[/TD]
[TD="align: right"]775
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEHEL
[/TD]
[TD]NYKU
[/TD]
[TD="align: right"]950
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SENRK
[/TD]
[TD]NYKU
[/TD]
[TD="align: right"]650
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEGOT
[/TD]
[TD]OOLU
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEHEL
[/TD]
[TD]OOLU
[/TD]
[TD="align: right"]950
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SENRK
[/TD]
[TD]OOLU
[/TD]
[TD="align: right"]950
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEGOT
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]500
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEHEL
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]600
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SENRK
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]650
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEGOT
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]450
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEHEL
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]500
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SENRK
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]550
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEGOT
[/TD]
[TD]NYKU
[/TD]
[TD="align: right"]550
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEHEL
[/TD]
[TD]NYKU
[/TD]
[TD="align: right"]700
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SENRK
[/TD]
[TD]NYKU
[/TD]
[TD="align: right"]500
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEGOT
[/TD]
[TD]OOLU
[/TD]
[TD="align: right"]900
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEHEL
[/TD]
[TD]OOLU
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SENRK
[/TD]
[TD]OOLU
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEGOT
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEHEL
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]1300
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SENRK
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]1300
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEGOT
[/TD]
[TD]COSU
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEHEL
[/TD]
[TD]COSU
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SENRK
[/TD]
[TD]COSU
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEGOT
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEHEL
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SENRK
[/TD]
[TD]HPLU
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEGOT
[/TD]
[TD]COSU
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEHEL
[/TD]
[TD]COSU
[/TD]
[TD="align: right"]700
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SENRK
[/TD]
[TD]COSU
[/TD]
[TD="align: right"]700
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEGOT
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]900
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEHEL
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]1200
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SENRK
[/TD]
[TD]MAEU
[/TD]
[TD="align: right"]1200
[/TD]
[/TR]
</tbody>[/TABLE]


In a separate tab I want to find the following results:

[TABLE="width: 570"]
<tbody>[TR]
[TD]TRADE
[/TD]
[TD]POL
[/TD]
[TD]Carrier 1
[/TD]
[TD]Carrier 2
[/TD]
[TD]Carrier 3
[/TD]
[TD]Carrier 4
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEGOT
[/TD]
[TD]HPLU
[/TD]
[TD]MAEU
[/TD]
[TD]NYKU
[/TD]
[TD]OOLU
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SEHEL
[/TD]
[TD]HPLU
[/TD]
[TD]MAEU
[/TD]
[TD]NYKU
[/TD]
[TD]OOLU
[/TD]
[/TR]
[TR]
[TD]AFRICA
[/TD]
[TD]SENRK
[/TD]
[TD]NYKU
[/TD]
[TD]MAEU
[/TD]
[TD]HPLU
[/TD]
[TD]OOLU
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEGOT
[/TD]
[TD]MAEU
[/TD]
[TD]HPLU
[/TD]
[TD]NYKU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SEHEL
[/TD]
[TD]MAEU
[/TD]
[TD]HPLU
[/TD]
[TD]NYKU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]AMNO
[/TD]
[TD]SENRK
[/TD]
[TD]NYKU
[/TD]
[TD]MAEU
[/TD]
[TD]HPLU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEGOT
[/TD]
[TD]COSU
[/TD]
[TD]OOLU
[/TD]
[TD]MAEU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SEHEL
[/TD]
[TD]COSU
[/TD]
[TD]OOLU
[/TD]
[TD]MAEU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]LATAM
[/TD]
[TD]SENRK
[/TD]
[TD]COSU
[/TD]
[TD]OOLU
[/TD]
[TD]MAEU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEGOT
[/TD]
[TD]COSU
[/TD]
[TD]MAEU
[/TD]
[TD]HPLU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SEHEL
[/TD]
[TD]COSU
[/TD]
[TD]HPLU
[/TD]
[TD]MAEU
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD]SPAC
[/TD]
[TD]SENRK
[/TD]
[TD]COSU
[/TD]
[TD]HPLU
[/TD]
[TD]MAEU
[/TD]
[TD] -
[/TD]
[/TR]
</tbody>[/TABLE]


So, to automize the values in carrier 1, carrier 2, carrier 3 & carrier 4 I need to look up "trade" and "POL", find the lowest value in column four and once this is found return the value in "carrier".

The masterdata is 2600 rows long, sometimes there is only one carrier, sometimes there are ten. I want to find the cheapeast one and display it in "carrier 1", I then want to display the second cheapest one in "carrier 2", third cheapest in "carrier 3" and fourth in "carrier 4".

Can this be done via vlookup+MIN somehow, or via index/match+MIN?

Many thanks in advance!!!
Daniel

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D
1	TRADE	POL	Carrier	Value
2	AFRICA	SEGOT	HPLU	700
3	AFRICA	SEHEL	HPLU	800
4	AFRICA	SENRK	HPLU	850
5	AFRICA	SEGOT	MAEU	750
6	AFRICA	SEHEL	MAEU	900
7	AFRICA	SENRK	MAEU	700
8	AFRICA	SEGOT	NYKU	775
9	AFRICA	SEHEL	NYKU	950
10	AFRICA	SENRK	NYKU	650
11	AFRICA	SEGOT	OOLU	800
12	AFRICA	SEHEL	OOLU	950
13	AFRICA	SENRK	OOLU	950
14	AMNO	SEGOT	HPLU	500
15	AMNO	SEHEL	HPLU	600
16	AMNO	SENRK	HPLU	650
17	AMNO	SEGOT	MAEU	450
18	AMNO	SEHEL	MAEU	500
19	AMNO	SENRK	MAEU	550
20	AMNO	SEGOT	NYKU	550
21	AMNO	SEHEL	NYKU	700
22	AMNO	SENRK	NYKU	500
23	LATAM	SEGOT	OOLU	900
24	LATAM	SEHEL	OOLU	1100
25	LATAM	SENRK	OOLU	1100
26	LATAM	SEGOT	MAEU	1100
27	LATAM	SEHEL	MAEU	1300
28	LATAM	SENRK	MAEU	1300
29	LATAM	SEGOT	COSU	800
30	LATAM	SEHEL	COSU	1000
31	LATAM	SENRK	COSU	1000
32	SPAC	SEGOT	HPLU	1000
33	SPAC	SEHEL	HPLU	1100
34	SPAC	SENRK	HPLU	1100
35	SPAC	SEGOT	COSU	800
36	SPAC	SEHEL	COSU	700
37	SPAC	SENRK	COSU	700
38	SPAC	SEGOT	MAEU	900
39	SPAC	SEHEL	MAEU	1200
40	SPAC	SENRK	MAEU	1200
Sheet1

Excel 2013/2016
	A	B	C	D	E	F
1	TRADE	POL	Carrier 1	Carrier 2	Carrier 3	Carrier 4
2	AFRICA	SEGOT	HPLU	MAEU	NYKU	OOLU
3	AFRICA	SEHEL	HPLU	MAEU	NYKU	NYKU
4	AFRICA	SENRK	NYKU	MAEU	HPLU	OOLU
5	AMNO	SEGOT	MAEU	HPLU	NYKU
6	AMNO	SEHEL	MAEU	HPLU	NYKU
7	AMNO	SENRK	NYKU	MAEU	HPLU
8	LATAM	SEGOT	COSU	OOLU	MAEU
9	LATAM	SEHEL	COSU	OOLU	MAEU
10	LATAM	SENRK	COSU	OOLU	MAEU
11	SPAC	SEGOT	COSU	MAEU	HPLU
12	SPAC	SEHEL	COSU	HPLU	MAEU
13	SPAC	SENRK	COSU	HPLU	MAEU
Sheet2

Cell Formulas
Range	Formula
C2	{=IFERROR(INDEX(Sheet1!$C$2:$C$40,MATCH($A2&$B2&SMALL(IF(Sheet1!$A$2:$A$40=$A2,IF(Sheet1!$B$2:$B$40=$B2,Sheet1!$D$2:$D$40)),COLUMN(Sheet1!N1)-COLUMN(Sheet1!$M$1)),Sheet1!$A$2:$A$40&Sheet1!$B$2:$B$40&Sheet1!$D$2:$D$40,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[thevidablue]

Thanks for the quick help! Tried to insert this exactly as advised, excel accepts the formula but IFERROR kicks in and returns a blank value. Any idea why?

 

[NeonRedSharpie]

Thanks for the quick help! Tried to insert this exactly as advised, excel accepts the formula but IFERROR kicks in and returns a blank value. Any idea why?

Most likely tab names? The formula calls Sheet1 and Sheet2 - I bet you've named your sheets otherwise.

 

[AlanY]

Thanks for the quick help! Tried to insert this exactly as advised, excel accepts the formula but IFERROR kicks in and returns a blank value. Any idea why?

have you confirmed with Ctrl+Shift+Enter?

 

[thevidablue]

Nope, they are correcly named and data is in the same cells. Very strange. Is it possible to send me a working workbook-copy somehow?

 

[AlanY]

Nope, they are correcly named and data is in the same cells. Very strange. Is it possible to send me a working workbook-copy somehow?

=IFERROR(INDEX(Sheet1!$C$2:$C$40,MATCH($A2&$B2&SMALL(IF(Sheet1!$A$2:$A$40=$A2,IF(Sheet1!$B$2:$B$40=$B2,Sheet1!$D$2:$D$40)),COLUMN(Sheet1!N1)-COLUMN(Sheet1!$M$1)),Sheet1!$A$2:$A$40&Sheet1!$B$2:$B$40&Sheet1!$D$2:$D$40,0)),"")

is an array formula that you have to enter with Ctrl+Shift+Enter together without the {}, the {} will appear automatically if enter correctly.

here is the sample file

https://drive.google.com/file/d/11aGqC6lzrw3H4sNI9RqCnSsliKzbiYmW/view?usp=sharing

 

[thevidablue]

That explains it and it works perfectly, thank you so much for the kind help!

 

[AlanY]

That explains it and it works perfectly, thank you so much for the kind help!

you're welcome