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

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

For some reason this did not work for me.

"Max" wrote:

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

Try this sample, which shows it working the way it should:
http://freefilehosting.net/download/3la03
index n match multi-criteria.xls

You might have been hit by the line break in the term below when you copied
n pasted the formula into B1 earlier:

... TRIM(Sheet2!$A$1:$A$100&" "&Sheet2!$B$1:$B$100) ..

There's a concat of a single space above which you need to amend in B1's
formula bar, post pasting it, before you confirm the formula with
CTRL+SHIFT+ENTER
(Refer to the sample's B1 in Sheet1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Jeremy" wrote:
For some reason this did not work for me.

Works great.

Thank you


"Max" wrote:

Try this sample, which shows it working the way it should:
http://freefilehosting.net/download/3la03
index n match multi-criteria.xls

You might have been hit by the line break in the term below when you copied
n pasted the formula into B1 earlier:

.. TRIM(Sheet2!$A$1:$A$100&" "&Sheet2!$B$1:$B$100) ..

There's a concat of a single space above which you need to amend in B1's
formula bar, post pasting it, before you confirm the formula with
CTRL+SHIFT+ENTER
(Refer to the sample's B1 in Sheet1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Jeremy" wrote:
For some reason this did not work for me.

Welcome. Do take a moment to press the "Yes" button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Jeremy" wrote:
Works great.

Thank you