Sorry me again...

It worked on one cell but then would not copy down correctly.

ie it matched the first lot to the name of the builder but it did not do it
for the rest of lots I know are listed for that same builder.


"Max" wrote:

One way is perhaps something along these lines

Supposing you have

in Sheet1
-------------
in cols A to C,
data from row2 down

Lot# Price Name
1110 3000 DEF
1111 4000 MNO
1112 1000 ABC
1113 2000 JKL
1114 1000 GHI

(Names in col C are assumed *unique*)

In Sheet2
------------
You have the names listed
down col A in ascending order,
data from row2 down

Name Lot# SettmtDate
ABC
DEF
GHI
JKL
MNO
etc

Put in B2:

=IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"",INDEX(Sheet1!A :A,MATCH(A2,Sheet1!C:C,0))
)

Copy down

This'll retrieve the lot #'s from Sheet1
for the names listed in col A, viz :

Name Lot# SettmtDate
ABC 1112
DEF 1110
GHI 1114
JKL 1113
MNO 1111
etc

Unmatched names, if any, will return blanks ("")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sal" wrote in message
...
I have a spreadsheet that contains information on lot number sold, price,
settlement date and who the purchaser is. I have a second spreadsheet
that
contains lot number, purchaser and settlement date.

I would like to be able to set up in the 2nd spreadsheet a formula so that
when a new lot number goes into the 1st it updates the second. The second
spreadsheet is sorted by Purchaser.

I was thinking possibly a comination of VLOOKUP and IF statements but am
now
completely at a loss as to where to start!