Select the MIN value in a data range

[moroandrea]

Hello all

I've defined a Name within my excel worksheet in this way

='RRD'!$H$1:INDEX('RRD'!$H:$H,MATCH(9.99E+307,'RRD'!$E:$E))

$H$H contains a series of values in the same RRD sheet, which are joined together with a pipe (as previously recommended) so I can use a non-dynamique more efficient Defined Name

The above Name is used in combination to a similar formula to extract some financial value related to the specific line obtained.

Everything was working fine so far, but I've come across the situation where more than a value can be returned for the same "unique" combination.

For instance a line of the $H$H range could be

Apple¦40791
Banana¦40791
Apple¦40791

Where 40791 is the Excel representation of a date.

The financial value is the cost of the item in a given date.

There is virtually no difference in the item, the problem is just the cost that I want to extract from dataset. Unless I don't order the item according to the criteria I want (smallest or largest) I can't obtain the number represented properly.

Now the question is.

In a formula like this

=IFERROR(INDEX(_Price, MATCH($F$2&"^"&$G$2,_Items,0)), "")

can I add a reference to the MIN to extract the _Prices smallest price?

I was thinking to use the array of cells and the MIN function, but honestly I don't know how to merge the two things.

Any idea or support is more than welcome.

Thanks
Andrea

[Bob Phillips]

Who suggested concatenating them with a Pipe? Seems guaranteed to make it more difficult to me. And where do the financials come into it?

[moroandrea]

It was on a different forum specifically designed for MS Excel. However, I can't find the thread at the moment.
The solution basically was based on the fact I was needing to obtain everything using just inline excel function rather than VBA.

Financial values come into play as a result. I mean given a list of products and dates, I need to pick up the item's cost for a given date, but not the first one ... the minimum.

[moroandrea]

I've found it

http://www.excelforum.com/excel-programming/787298-how-to-use-index-and-match-in-vba-to-retrieve-a-value-using-three-columns-as-source.html

[Bob Phillips]

I would use an array formula like so

=INDEX(fin_range,MATCH(1,(item_range="item")*(date_range=date),0))

But this assumes you don't concatenate with a pipe, but have the data in separate columns.

[moroandrea]

It was used to be in that way, but as the information in the raw data worksheet need to be updated frequently and the rows increase, in order to avoid problem with the dynamic data names, I've been told to create a new one which rely on the concatenated string.

In effect, the performances slightly increased, so I really don't want to roll back to the previous solution solving a problem but reintroducing a new one.

Any other idea?

[Bob Phillips]

Then you would use a formula something like this

=INDEX(fin_Range,MATCH(1,(LEFT(items_range,FIND("|",items_range)-1)="Apples")*(--MID(items_range,FIND("|",items_range)+1,255)=date),0))

Tell me, why didn't you go back to ExcelForum and ask the genius who suggested concatenating it how to get the answer?