lookup value and date from a list with restrictions
Try Auto Filter
" wrote:
Hello,
I have the following problem.
From a Data list that has a large amount of information registrated,
such as:
Stock Date Value Operation
A 20-jan-07 100 buy
B 25-jan-07 90 sell
A 5-feb-07 150 buy
C 10-feb-07 200 sell
A 5-Mar-07 200 buy
I need to recover from the Data list all information available about
Stock A.
What do you recommend?
If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the
date 20-jan-07 would appear
If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha
value 100 would appear.
The problem is, how do I write a formula that finds the rest of the
information availlable of Stock A.
Previously I posted a similar question with two results.
One suggested using Autofilter. For a manual solution thats ok, but
this must be automatic. The Data list has a large amount of registers
that grow on a daily basis.
The second suggested using = LOOKUP (2, 1/(stock="A"), Value).
I don't understand how this works, allow it does find the last value
for A, but not the second. Please consider that more than three
registers for A might exist. This is only an example.
Thank you for the help,
avializq
|