Minimum v-lookup using multiple conditions

[Mezner]

Hi all,

I've searched the forum for similair instances. I found some, but they don't include MULTIPLE conditions. Whatever I'm doing is not working and sends a 'Value?' error message.


I have a large database of origin/destinations. There may be as many as 10 different 'rates' for each origin/destination combo and I want to find the smallest value for each. Below is just a tiny example of the table.

Essentially I'm creating a "grid" to help me filter rapidly. The grid for the example would look something like below. I was trying to create an IF statment that I could drag across all the cells MIN(IF(Origin=$A$2 AND Destination = B1,))). That way I could drag it across, say, 20 different desinations for the same origin.

	Fargo	Grand Forks
Minneapolis	x	x

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Origin	Destination	Carrier	Rate
Minneapolis	Fargo	A	0.01
Minneapolis	Grand Forks	B	0.015
Minneapolis	Grand Forks	C	0.012

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[DRSteele]

This might work. Keep in mind that these array formulas must be entered with CTRL + SHFT + Enter. (You can enter the first one properly in C16 and copy it across and down.)

Excel 2012
	A	B	C	D	E	F	G
1		Origin	Destination	Carrier	Rate
2		Minneapolis	Fargo	A	0.010
3		Minneapolis	Grand Forks	B	0.015
4		Minneapolis	Grand Forks	C	0.012
5		Calgary	Edmonton	A	0.015
6		Calgary	Fargo	A	0.014
7		Calgary	Fargo	B	0.016
8		Calgary	Grand Forks	B	0.013
9		Vancouver	Chicago	C	0.100
10		Montreal	Vicotria	A	0.150
11		Vicotria	Chicago	A	0.016
12		Vicotria	Chicago	C	0.018
13
14					destination
15			Fargo	Grand Forks	Edmonton	Chicago	Vicotria
16		Minneapolis	0.010	0.012	0.000	0.000	0.000
17		Calgary	0.014	0.013	0.015	0.000	0.000
18	origin	Vancouver	0.000	0.000	0.000	0.100	0.000
19		Montreal	0.000	0.000	0.000	0.000	0.150
20		Vicotria	0.000	0.000	0.000	0.016	0.000
21
22	N.B. Formulas are CTRL+SHFT+ENTR
Sheet28

Cell Formulas
Range	Formula
C16	{=MIN(IF(($B$2:$B$12=$B16)*($C$2:$C$12=C$15),$E$2:$E$12))}
C17	{=MIN(IF(($B$2:$B$12=$B17)*($C$2:$C$12=C$15),$E$2:$E$12))}
C18	{=MIN(IF(($B$2:$B$12=$B18)*($C$2:$C$12=C$15),$E$2:$E$12))}
C19	{=MIN(IF(($B$2:$B$12=$B19)*($C$2:$C$12=C$15),$E$2:$E$12))}
C20	{=MIN(IF(($B$2:$B$12=$B20)*($C$2:$C$12=C$15),$E$2:$E$12))}
D16	{=MIN(IF(($B$2:$B$12=$B16)*($C$2:$C$12=D$15),$E$2:$E$12))}
D17	{=MIN(IF(($B$2:$B$12=$B17)*($C$2:$C$12=D$15),$E$2:$E$12))}
D18	{=MIN(IF(($B$2:$B$12=$B18)*($C$2:$C$12=D$15),$E$2:$E$12))}
D19	{=MIN(IF(($B$2:$B$12=$B19)*($C$2:$C$12=D$15),$E$2:$E$12))}
D20	{=MIN(IF(($B$2:$B$12=$B20)*($C$2:$C$12=D$15),$E$2:$E$12))}
E16	{=MIN(IF(($B$2:$B$12=$B16)*($C$2:$C$12=E$15),$E$2:$E$12))}
E17	{=MIN(IF(($B$2:$B$12=$B17)*($C$2:$C$12=E$15),$E$2:$E$12))}
E18	{=MIN(IF(($B$2:$B$12=$B18)*($C$2:$C$12=E$15),$E$2:$E$12))}
E19	{=MIN(IF(($B$2:$B$12=$B19)*($C$2:$C$12=E$15),$E$2:$E$12))}
E20	{=MIN(IF(($B$2:$B$12=$B20)*($C$2:$C$12=E$15),$E$2:$E$12))}
F16	{=MIN(IF(($B$2:$B$12=$B16)*($C$2:$C$12=F$15),$E$2:$E$12))}
F17	{=MIN(IF(($B$2:$B$12=$B17)*($C$2:$C$12=F$15),$E$2:$E$12))}
F18	{=MIN(IF(($B$2:$B$12=$B18)*($C$2:$C$12=F$15),$E$2:$E$12))}
F19	{=MIN(IF(($B$2:$B$12=$B19)*($C$2:$C$12=F$15),$E$2:$E$12))}
F20	{=MIN(IF(($B$2:$B$12=$B20)*($C$2:$C$12=F$15),$E$2:$E$12))}
G16	{=MIN(IF(($B$2:$B$12=$B16)*($C$2:$C$12=G$15),$E$2:$E$12))}
G17	{=MIN(IF(($B$2:$B$12=$B17)*($C$2:$C$12=G$15),$E$2:$E$12))}
G18	{=MIN(IF(($B$2:$B$12=$B18)*($C$2:$C$12=G$15),$E$2:$E$12))}
G19	{=MIN(IF(($B$2:$B$12=$B19)*($C$2:$C$12=G$15),$E$2:$E$12))}
G20	{=MIN(IF(($B$2:$B$12=$B20)*($C$2:$C$12=G$15),$E$2:$E$12))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Mezner]

TY. I don't know why it wasn't working before (I knew it had to be an array), but I followed your formula structure and added my named tables/fields to make it work. Sweet!

My formula looks a little like this:

{=MIN(IF((Freights[Origin]=$F$3)*(Freights[Destination]=G$2),Freights[Rate]))}

Comes out pretty nifty, especially since I have over 2,200 rows of data (and only 30 destinations).

 

[DRSteele]

Good show! Glad I helped,

 

[Aladin Akyurek]

TY. I don't know why it wasn't working before (I knew it had to be an array), but I followed your formula structure and added my named tables/fields to make it work. Sweet!

My formula looks a little like this:

{=MIN(IF((Freights[Origin]=$F$3)*(Freights[Destination]=G$2),Freights[Rate]))}

Comes out pretty nifty, especially since I have over 2,200 rows of data (and only 30 destinations).

{=IFERROR(1/(1/MIN(IF(Freights[Origin]=$F$3,IF(Freights[Destination]=G$2,Freights[Rate])))),"Not available")}

does not return a possibly non-admissible zero as result.