1
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> )
is there a way to define the array inline? – Stephen – 2011-09-19T20:00:17.763
I'm not sure what you mean by inline. Do you mean list down all the prices for a particular product in order? – Ellesa – 2011-09-19T20:08:51.120
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 – 2011-09-19T20:10:21.040
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 – 2011-09-19T20:11:11.617
Along the lines of =MIN(IF($A=D3,$C,"")) or =MIN(IF(A1:A234=D3,C1:C234,"")) – Stephen – 2011-09-19T20:11:15.110
Yep, that works (the 2nd formula). So for the screenshot above, you can use
=MIN(IF(A2:A25=D3,B2:B25,""))
. The named ranges just makes it easier to type the formulas. I'll edit my answer to clarify. :) – Ellesa – 2011-09-19T20:16:31.903