moroandrea
09-05-2011, 07:17 AM
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
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