Assuming that col B in Sheet2 actually contains text numbers (with the
leading zeros)

In Sheet1,
Put in B1,
then press CTRL+SHIFT+ENTER to confirm the formula (ie "array-enter"):
=INDEX(Sheet2!$C$1:$C$100,MATCH(TRIM(A1),TRIM(Shee t2!$A$1:$A$100&"
"&Sheet2!$B$1:$B$100),0))
Copy B1 down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Jeremy" wrote:
I am tring to transfer data from one worksheet to another given certain
criteria using a formula. Below is an example.


Sheet one
a b
1 400 016
2 400 017
3 1028 0143
4 4567 0252
5 23949 110


Sheet two
a b c
1 400 016 car126
2 400 017 car127
3 1028 0143 car128
4 4567 0252 car129
5 23949 1101 car130


Sheet one results after formula.
a b
1 400 016 car126
2 400 017 car127
3 1028 0143 car128
4 4567 0252 car129
5 23949 110 car130