Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a spreadsheet which contains a list of products from different vendors. It is possible, even likely, that the product name will not be unique.

On another spreadsheet, I want to import the minimum price the product is available for. I know how to use MIN() to achieve this so I attempted to merge this with using VLOOKUP but that always returns the first value found, not the minimum one. My VLOOKUP uses exact matching, and works correctly for every unique item.

How can I achieve this result in a single formula? I can see a solution that involves splitting up my master price list but I would strongly prefer not to do this.

I'm thinking that I can detect WHEN this occurs by using

=IF( COUNTIFS(B2:B1193,"="&$C2) > 1, <do the multiple version>, <do the vlookup> )
share|improve this question

2 Answers 2

up vote 1 down vote accepted

You can use an array formula:

=MIN(IF(products=D3,prices,""))

or

=MIN(IF(products="Product A",prices,""))

or

=MIN(IF(A2:A25=D3,B2:B25,""))

where D3 / "Product A" is the name of a product as it appears on your master list.

In the example below, products refers to A1:A25 and prices refers to B1:B25.

enter image description here

share|improve this answer
    
is there a way to define the array inline? –  Stephen Sep 19 '11 at 20:00
    
I'm not sure what you mean by inline. Do you mean list down all the prices for a particular product in order? –  Ellesa Sep 19 '11 at 20:08
    
I mean not having to define the array outside of the formula via create a name or some other dialogue, but have it right in the formula –  Stephen Sep 19 '11 at 20:10
    
For the formula above, you just have to commit it using Ctrl + Shift + Enter, and you'll immediately get the lowest price for a product. –  Ellesa Sep 19 '11 at 20:11
    
Along the lines of =MIN(IF($A=D3,$C,"")) or =MIN(IF(A1:A234=D3,C1:C234,"")) –  Stephen Sep 19 '11 at 20:11

I created a hidden column in my price list that contains the unique combination of product name and supplier name plus a character that doesn't appear anywhere. I'm now doing a vlookup on that, which solves the problem.

The hidden column resembles =$A$1 & "þ" & $B2 ...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.