wrote in message
ps.com...
On 4 jun, 17:27, "T. Valko" wrote:
How many rows of data are there? If there are 1000's then Autofilter is
your
best option.

Biff

wrote in message

ps.com...

On 4 jun, 16:06, Teethless mama
wrote:
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

TM,
I understand that Auto Filter helps, but I'm looking for a formula. My
Data list has too many records, and I need to look up many different
field matches.
avializq

Biff,
The amount of rows grow daily. Eventually, there will be 1000 rows,
but the amount of rows that meet the criteria (for example Stock A)
should not exceed 10.
The problem I see about using auto filter is that there are many
different stocks and they change in time.
avializq

Here's a sample file that demonstates this:

Delete_Me.xls 18kb

http://cjoint.com/?gffLHM0ch3

Enter a stock symbol in cell G2 and the bordered table will fill in.

I've used a few defined names. To see these goto the menu
InsertNameDefine.

I entered the array formula** in cell H2 then copied across to J2 then down
to row 11.

As you add new data to the stock table the ranges will automatically adjust.
This is based on the assumption that your stock table will be a contiguous
block with no empty cells within. You'll have to see if this impacts the
performance of your file.

You said that there should only be about 10 entries that meet the criteria
so I've copied the formula to only 10 rows.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

P.S. - that file name is for my own use! I have 100's of sample files and
trying to come up with new unique names is a challenge. When I post sample
files they're unique and specific to the subject of the post. After a few
days I delete them so the file name lets me know which files I can delete.

Biff