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!