Find lowest value depending on conditions

[Tybudd]

Hey guys, I have a formula question.
This is the column setup for

Sheet Name=Prices

PRODUCT ID

DESCRIPTION

EFFECTIVE FROM DATE

Purchasing UoM

PARTNER / VENDOR

PURCHASE PRICE

Product ID = List of Items by name
Description = Irrelevant
Effective Date = At what point the data comes into place.
Purchasing UoM = Irrelevant to the point
Partner/ Vender = Vendor that Supplies listed Product Product ID, and for what Price
Purchase Price = Price by way of Vendor, for said Product ID.


On another sheet I have:
Sheet Name=PRODUCTS

ID

NAME

ON INVENTORY SHEETS

PARTNER / VENDOR

ID=same as Product type
Name = Description
ON INVENTORY SHEETS = Irrelevant
PARTNER / VENDOR = DESTINATION OF NEEDED FORMULA


Goal is to check on the Prices Page, checking according to "ID", and checking who has the lowest price, of there most current Effective Date.
So it would check all Partners that have the specific ID being looked for, then check the most current Effective date, then check who has the lowest price that meet those criteria, and return that value, or Vendor name.


Key Note for understanding, the Prices sheet is a running vertical log, recording each time an ID has a price change, by each vendor, and at what point (date), its effective.

 

[Tybudd]

Too complex? Or not clear?

 

[AhoyNC]

If you can add two more columns to your price sheet. One for the most current date for Vendor and one for their current price. Then maybe something like below.
I did add a column on your product sheet to show if more than 1 vendor had the same low price. Not knowing how you have things set up and what ver. of Excel you are using I didn't added an formulas to list the other vendors if there are any.
Also, you will need one of the later versions of Excel that has the functions MAXIFS and MINIFS (if not there are some changes that we can make).

In the example the formulas in D12 and F12 are array formulas that must be entered with CTRL-SHIFT-ENTER and then drag down as need for your products.

Book1
	A	B	C	D	E	F	G	H
1	PRODUCT ID	DESCRIPTION	EFFECTIVE FROM DATE	Purchasing UoM	PARTNER / VENDOR	PURCHASE PRICE	Current Date	Min Price
2	1		7/5/2019		Vendor1	200	11/30/2019	342
3	1		9/16/2019		Vendor2	342	9/16/2019	342
4	2		8/12/2019		Vendor2	321	8/12/2019	321
5	2		5/5/2019		Vendor4	406	10/30/2019	320
6	1		11/30/2019		Vendor1	342	11/30/2019	342
7	2		10/30/2019		Vendor4	320	10/30/2019	320
8	2		10/25/2019		Vendor1	462	10/25/2019	462
9
10
11	ID	NAME	ON INVENTORY SHEETS	PARTNER / VENDOR	Price	More than 1 Vendor
12	1			Vendor1	342	Yes
13	2			Vendor4	320
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=MAXIFS($C$2:$C$8,$A$2:$A$8,$A2,$E$2:$E$8,$E2)
H2:H8	H2	=MINIFS($F$2:$F$8,$A$2:$A$8,$A2,$C$2:$C$8,$G$2:$G$8)
D12, D13	D12	{=INDEX($E$2:$E$8,SMALL(IF(MIN(IF($A$2:$A$8=$A12,$H$2:$H$8))=$H$2:$H$8,ROW($H$2:$H$8)-ROW($H$2)+1),1))}
E12, E13	E12	=MINIFS($H$2:$H$8,$A$2:$A$8,$A12)
F12, F13	F12	{=IF(SUM(IF(FREQUENCY(IF($A$2:$A$8=$A12,IF($H$2:$H$8=$E12,MATCH($E$2:$E$8&"\"&$H$2:$H$8,$E$2:$E$8&"\"&$H$2:$H$8,0))),ROW($E$2:$E$8)-ROW($E$2)),1))>1,"Yes","")}
G12	G12	{=IFERROR(INDEX($C$2:$F$8,SMALL(IF(FREQUENCY(MATCH($E$2:$E$8,$E$2:$E$8,0),ROW($E$2:$E$8)-ROW($E$2)+1),ROW($E$2:$E$8)-ROW($E$2)+1),COLUMNS($C$15:G15)),MATCH($B27,$C$1:$F$1,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DanteAmor]

Check if the following is what you need.
Is an array formula:

Book1
	A	B	C	D	E	F
1	PRODUCT ID	DESCRIPTION	EFFECTIVE FROM DATE	Purchasing UoM	PARTNER / VENDOR	PURCHASE PRICE
2	123		15/06/2019		Ven1	49
3	123		16/06/2019		Ven2	49
4	123		16/06/2019		Ven3	51
5	246		20/09/2019		ven7	101
6	246		21/09/2019		ven8	80
7	246		22/09/2019		ven9	80
8	246		23/09/2019		ven10	81
9
10
11	ID	NAME	ON INVENTORY SHEETS	PARTNER / VENDOR	LOWEST PRICE
12	123			Ven2	49
13	246			ven9	80
Hoja13

Cell Formulas
Range	Formula
D12:D13	D12	{=LOOKUP(2,1/(($A$2:$A$8=$A12)*($F$2:$F$8=MIN(IF($A$2:$A$8=$A12,$F$2:$F$8)))*($C$2:$C$8)),$E$2:$E$8)}
E12:E13	E12	{=LOOKUP(2,1/(($A$2:$A$8=$A12)*($F$2:$F$8=MIN(IF($A$2:$A$8=$A12,$F$2:$F$8)))*($C$2:$C$8)),$F$2:$F$8)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[AhoyNC]

Dante - Not sure if your formulas will work. It looks like the same vendor(s) (per OP) could show up multiply times under a product with different effective dates. So, would need to find the most current price for each vendor based on effective date and then the lowest price.

 

[DanteAmor]

Dante - Not sure if your formulas will work. It looks like the same vendor(s) (per OP) could show up multiply times under a product with different effective dates. So, would need to find the most current price for each vendor based on effective date and then the lowest price.

Hi @AhoyNC, thanks for the observation, but I think the formula brings only one vendor and only one price. That is the goal.

If there is a tie of vendor, dates and prices, we would need to know the tiebreaker criteria. Or that the OP requests that all matches be displayed.

Book1
	A	B	C	D	E	F
1	PRODUCT ID	DESCRIPTION	EFFECTIVE FROM DATE	Purchasing UoM	PARTNER / VENDOR	PURCHASE PRICE
2	123		16/06/2019		Ven1	49
3	123		16/06/2019		Ven2	49
4	123		16/06/2019		Ven3	49
5	246		20/09/2019		ven7	101
6	246		20/09/2019		ven8	82
7	246		20/09/2019		ven9	80
8	246		20/09/2019		ven10	81
9
10
11	ID	NAME	ON INVENTORY SHEETS	PARTNER / VENDOR	LOWEST PRICE
12	123			Ven3	49
13	246			ven9	80
Hoja13

Cell Formulas
Range	Formula
D12:D13	D12	{=LOOKUP(2,1/(($A$2:$A$8=$A12)*($F$2:$F$8=MIN(IF($A$2:$A$8=$A12,$F$2:$F$8)))*($C$2:$C$8)),$E$2:$E$8)}
E12:E13	E12	{=LOOKUP(2,1/(($A$2:$A$8=$A12)*($F$2:$F$8=MIN(IF($A$2:$A$8=$A12,$F$2:$F$8)))*($C$2:$C$8)),$F$2:$F$8)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

If you have an example where the formula does not work, I would greatly appreciate it if you can share it with me.

 

[AhoyNC]

See below. Vendor1 has two effect dates. Your formula pulls the lowest price, but not the most current date. OP states that the vendor may show up multiply times with different effective dates for same product. Sounds like the price sheet is a history of vendor pricing for product and they nee to check and find the most current price for each vendor and then the lowest.
Using your data below if we show vendor1 with different effective dates for product 123 it doesn't pick up the most current price for vendor1, just the lowest value.
Price should be 48 and not 46.
OP may need to clarify how price sheet is actual set up.

Book1
	A	B	C	D	E	F
1	PRODUCT ID	DESCRIPTION	EFFECTIVE FROM DATE	Purchasing UoM	PARTNER / VENDOR	PURCHASE PRICE
2	123		6/16/2019		Ven1	46
3	123		6/16/2019		Ven2	49
4	123		10/30/2019		Ven1	48
5	246		9/20/2019		ven7	101
6	246		9/20/2019		ven8	82
7	246		9/20/2019		ven9	80
8	246		9/20/2019		ven10	81
9
10
11	ID	NAME	ON INVENTORY SHEETS	PARTNER / VENDOR	LOWEST PRICE
12	123			Ven1	46	<==Should be 48
13	246			ven9	80
Sheet1

Cell Formulas
Range	Formula
D12:D13	D12	{=LOOKUP(2,1/(($A$2:$A$8=$A12)*($F$2:$F$8=MIN(IF($A$2:$A$8=$A12,$F$2:$F$8)))*($C$2:$C$8)),$E$2:$E$8)}
E12:E13	E12	{=LOOKUP(2,1/(($A$2:$A$8=$A12)*($F$2:$F$8=MIN(IF($A$2:$A$8=$A12,$F$2:$F$8)))*($C$2:$C$8)),$F$2:$F$8)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DanteAmor]

Thanks Ahoy, for taking the time to review it, it seems to me that now the formula does work.
But there is the detail of a tie, same ID, same date, same price.
In this case the formula returns the data below the list.

Book1
	A	B	C	D	E	F
1	PRODUCT ID	DESCRIPTION	EFFECTIVE FROM DATE	Purchasing UoM	PARTNER / VENDOR	PURCHASE PRICE
2	123		16/06/2019		Ven1	40
3	123		16/06/2019		Ven2	49
4	123		30/10/2019		Ven1	45
5	123		30/10/2019		Ven1	47
6	123		30/10/2019		Ven3	45
7	123		30/10/2019		Ven3	47
8	246		20/09/2019		ven8	82
9	246		20/09/2019		ven9	80
10	246		20/09/2019		ven10	81
11
12
13	ID	NAME	ON INVENTORY SHEETS	PARTNER / VENDOR	LOWEST PRICE
14	123			Ven3	45
15	246			ven9	80
Hoja13

Cell Formulas
Range	Formula
D14:D15	D14	{=LOOKUP(2,1/(($A$2:$A$10=$A14)*($F$2:$F$10=MIN(IF($A$2:$A$10=$A14,IF($C$2:$C$10=MAX(IF($A$2:$A$10=$A14,$C$2:$C$10)),$F$2:$F$10))))),$E$2:$E$10)}
E14:E15	E14	{=LOOKUP(2,1/(($A$2:$A$10=$A14)*($F$2:$F$10=MIN(IF($A$2:$A$10=$A14,IF($C$2:$C$10=MAX(IF($A$2:$A$10=$A14,$C$2:$C$10)),$F$2:$F$10))))),$F$2:$F$10)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Tybudd]

Here is the link to my document, on the Products Tab, I attempted to follow Ahoy's example to be displayed on D4, and the example of DanteAmor on D5, but I couldn't get neither to work, what do you see that I did wrong?Master Inventory Doc

 

[DanteAmor]

This is the correct formula.

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

Book1
	A	B	C	D
3	ID	NAME	ON INVENTORY SHEETS	PARTNER / VENDOR
4	BOX001	Box 20 Green	BOX001	Stellar Snacks
PRODUCTS

Cell Formulas
Range	Formula
D4	D4	{=LOOKUP(2,1/((T_PRI[PRODUCT ID]=T_PR[[#This Row],[ID]])*(T_PRI[PURCHASE PRICE]=MIN(IF(T_PRI[PRODUCT ID]=T_PR[[#This Row],[ID]],IF(T_PRI[EFFECTIVE FROM DATE]=MAX(IF(T_PRI[PRODUCT ID]=T_PR[[#This Row],[ID]],T_PRI[EFFECTIVE FROM DATE])),T_PRI[PURCHASE PRICE]))))),T_PRI[PARTNER / VENDOR])}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Your file with formula:

Dropbox - File Deleted - Simplify your life

www.dropbox.com