How to find lowest price for product and then lookup associated data

[JDW96]

Hi everyone!

Hoping you can help me. I'm trying to build a pricing database for building supplies. I have prices from many suppliers for products and would like to have a page displaying only the lowest price for each product, along with relevant data such as product code and pack size.

I have structured the data with a new line for each price to allow me to have more information for each supplier, e.g. product code.



I have worked on a formula to return the lowest price for each product:

=MIN(IF(Sheet1!$A$1:$A$10000=A2,Sheet1!$F$2:$F$10000))



Alternatively this formula could be used:

=AGGREGATE(15,6,Sheet1!$F$2:$F$401/(Sheet1!$A$2:$A$401=A2)/(Sheet1!$F$2:$F$401>0),1)



Once I have this minimum price do you have any ideas as to how I could then pull though the other data I need. I can do it with a VLOOKUP on the price value, but then if I had any items with exactly the same price in the data it would return the first result rather than the one I am looking for. The second attached spreadsheet demonstrates this problem


Many thanks in advance for your help, I really appreciate it!

 

[Eric W]

Welcome to the Board!

Consider:

	A	B	C	D	E	F	G	H	I	J	K
Product	Vendor	Date	Price	Product	Min Price	Vendor	Date
A	Al	B	Doug
B	Cal
A	Bob
B	Doug
C	Al
A	Cal

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]4-Jan[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6-Jan[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]K2[/TH]
[TD="align: left"]=SUMIFS(C:C,A:A,H2,F:F,I2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]{=INDEX($B$2:$B$7,MATCH(H2&"|"&I2,$A$2:$A$7&"|"&$F$2:$F$7,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I assume your MIN formula is in I2. Then you can use the J2 array formula to find the match. If the value you are looking up is numeric (dates are numbers), AND there are no duplicate Product/Price combinations, you can use the somewhat simpler formula in K2. If there are duplicate Product/Price combinations, use the J2 formula, and it will return the first matching combination.

Hope this helps!