Need Formula Help.

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I have two workbooks (A and B). I need to create a formula
in workbook B that will look up, compare and match
FirstName, LastName and Company in both wrbk A and B then
return the Company Name from wrbk A.

During a conversion 1,200 contacts were given the same
Company name. I have the old contacts with the correct
Company name and need to compare the two wrbks to automate
the look up and replace the wrong Company name in wrbk B
with the correct Company name from wrkb A

Is this possible?
 
Hi
assumptions:
- col. A: firstname
- col B: lastname
- col C: company name
- I assume that both lists are in the same workbook. Change your
references accodingly:

Enter the following array formula (entered with CTRL+SHIFT+ENTER) in
D1 on your worksheetB:

=MATCH('worksheetA'!$C$1:$C$1200,MATCH(A1&B1,'worksheetA'!$A$1:$A$1200
& 'worksheetA'!$B$1:$B$1200,0))
and copy this down
 
Thank you Frank.

Here's how my formula looks:
=MATCH('Old Contacts'!$C$2:$C$10076,MATCH(A2&B2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'!$B$2:$B$10076,0))

Unfortunately, it's returning #NA

What am I doing wrong?

sandy
 
Hi
sorry my fault. Try
=INDEX('Old Contacts'!$C$2:$C$10076,MATCH(A2&B2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'!$B$2:$B$10076,0))
 
It still doesn't work. :-(

I have worksheet A ("Old Contacts") and worksheet B
("hidden columns"). Put your newest formula on worksheet B
("hidden columns") Cell D2.

thanks,
sandy
 
Hi
an what is your result?
- an error
- wrong data?

Column A and column B on both sheets have to store the same kind of
information
 
The result is #NA

Columns A and B in both worksheets are First Name and Last
Name.

Are you saying they need to have the exact same
information line by line?

thanks
 
Hi
yes: to find a match firstname and lastname have to be identical. The
can be in different rows in sheet1 and sheet2 but they have to be an
exact match to be found
 
I see, well the two document sheets are not exact. That's
why I'd like Excel to "look at the cells, see if there's a
match and return the "old contact" company name to replace
the "hidden columns" company name.

If I posted the document somewhere on the net, would
looking at it help?

sandy
 
Hi
how would you define a match. Which cells have to be equal?

If you like, puth the file on the net or email it to me 8please don't
attach a file to this NG)
email: frank[dot]kabel[at]freenet[dot]de
 
I want it to look at:

Sheet 1 "Old Contacts" and Sheet 2 "Hidden Columns" look
at FirstName and LastName on Sheet 2, compare it to
FirstName and LastName on Sheet 1

example:
Sheet 2 looks at A2 "Lisa" B2 "Collins" then it looks at
Sheet 1 "Old Contacts"
to find any instance of Lisa Collins on Sheet 1, look at
the company name in "C" and return the Company name in C2,
sheet 2

does this make sense?
sandy
-----Original Message-----
Hi
how would you define a match. Which cells have to be equal?

If you like, puth the file on the net or email it to me 8please don't
attach a file to this NG)
email: frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany

I see, well the two document sheets are not exact. That's
why I'd like Excel to "look at the cells, see if there's a
match and return the "old contact" company name to replace
the "hidden columns" company name.

If I posted the document somewhere on the net, would
looking at it help?

sandy
Click to expand...
identical.
The
Click to expand...
the same kind
of
.
Click to expand...
 
Hi
but then the formula should do. You may email me your file :-) as the
formula
=INDEX('Old Contacts'!$C$2:$C$10076,MATCH(A2&B2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'!$B$2:$B$10076,0))

should work

--
Regards
Frank Kabel
Frankfurt, Germany

I want it to look at:

Sheet 1 "Old Contacts" and Sheet 2 "Hidden Columns" look
at FirstName and LastName on Sheet 2, compare it to
FirstName and LastName on Sheet 1

example:
Sheet 2 looks at A2 "Lisa" B2 "Collins" then it looks at
Sheet 1 "Old Contacts"
to find any instance of Lisa Collins on Sheet 1, look at
the company name in "C" and return the Company name in C2,
sheet 2

does this make sense?
sandy
-----Original Message-----
Hi
how would you define a match. Which cells have to be equal?

If you like, puth the file on the net or email it to me 8please don't
attach a file to this NG)
email: frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany

I see, well the two document sheets are not exact. That's
why I'd like Excel to "look at the cells, see if there's a
match and return the "old contact" company name to replace
the "hidden columns" company name.

If I posted the document somewhere on the net, would
looking at it help?

sandy
-----Original Message-----
Hi
yes: to find a match firstname and lastname have to be identical.
The can be in different rows in sheet1 and sheet2 but they have to
be an exact match to be found

--
Regards
Frank Kabel
Frankfurt, Germany


Sandy wrote:
The result is #NA

Columns A and B in both worksheets are First Name and Last
Name.

Are you saying they need to have the exact same
information line by line?

thanks
-----Original Message-----
Hi
an what is your result?
- an error
- wrong data?

Column A and column B on both sheets have to store the same kind
of information


--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag [email protected]...
It still doesn't work. :-(

I have worksheet A ("Old Contacts") and worksheet B
("hidden columns"). Put your newest formula on worksheet B
("hidden columns") Cell D2.

thanks,
sandy
-----Original Message-----
Hi
sorry my fault. Try
=INDEX('Old Contacts'!$C$2:$C$10076,MATCH (A2&B2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'! $B$2:$B$10076,0))


--
Regards
Frank Kabel
Frankfurt, Germany


Sandy wrote:
Thank you Frank.

Here's how my formula looks:
=MATCH('Old Contacts'!$C$2:$C$10076,MATCH (A2&B2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'! $B$2:$B$10076,0))

Unfortunately, it's returning #NA

What am I doing wrong?

sandy
-----Original Message-----
Hi
assumptions:
- col. A: firstname
- col B: lastname
- col C: company name
- I assume that both lists are in the same workbook. Change
your references accodingly:

Enter the following array formula (entered with
CTRL+SHIFT+ENTER) in D1 on your worksheetB:

=MATCH('worksheetA'!$C$1:$C$1200,MATCH (A1&B1,'worksheetA'!
$A$1:$A$1200 & 'worksheetA'!$B$1:$B$1200,0))
and copy this down

--
Regards
Frank Kabel
Frankfurt, Germany


Sandy wrote:
I have two workbooks (A and B). I need to create a formula
in workbook B that will look up, compare and match
FirstName, LastName and Company in both wrbk A and B then
return the Company Name from wrbk A.

During a conversion 1,200 contacts were given the same
Company name. I have the old contacts with the correct
Company name and need to compare the two wrbks to automate
the look up and replace the wrong Company name in wrbk B
with the correct Company name from wrkb A

Is this possible?

.

.


.

.
Click to expand...

.
Click to expand...
Click to expand...
 
Cell A2&B2 on sheet 2 go to sheet 1, all cells in A&B, and
look for a match, if it matches, then return the Company
Name in the formula cell.

-----Original Message-----
Hi
how would you define a match. Which cells have to be equal?

If you like, puth the file on the net or email it to me 8please don't
attach a file to this NG)
email: frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany

I see, well the two document sheets are not exact. That's
why I'd like Excel to "look at the cells, see if there's a
match and return the "old contact" company name to replace
the "hidden columns" company name.

If I posted the document somewhere on the net, would
looking at it help?

sandy
Click to expand...
identical.
The
Click to expand...
the same kind
of
.
Click to expand...
 
Hi
Problem solved :-)
you have a different column order in both sheets. That is:
old_contacts:
- col. A = Firstname
- col. B = Lastname

New sheet:
- col. A = Lastname
- col. B = Firstname

so the following formula would do:
=INDEX('Old Contacts'!$C$2:$C$10076,MATCH(B2&A2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'!$B$2:$B$10076,0))

To prevent the error output you may use the following (also array
entered):

=IF(ISNA(MATCH(B2&A2,'Old Contacts'!$A$2:$A$10076 & 'Old
Contacts'!$B$2:$B$10076,0)),"no match",INDEX('Old
Contacts'!$C$2:$C$10076,MATCH(B2&A2,'Old Contacts'!$A$2:$A$10076 & 'Old
Contacts'!$B$2:$B$10076,0)))


--
Regards
Frank Kabel
Frankfurt, Germany

I want it to look at:

Sheet 1 "Old Contacts" and Sheet 2 "Hidden Columns" look
at FirstName and LastName on Sheet 2, compare it to
FirstName and LastName on Sheet 1

example:
Sheet 2 looks at A2 "Lisa" B2 "Collins" then it looks at
Sheet 1 "Old Contacts"
to find any instance of Lisa Collins on Sheet 1, look at
the company name in "C" and return the Company name in C2,
sheet 2

does this make sense?
sandy
-----Original Message-----
Hi
how would you define a match. Which cells have to be equal?

If you like, puth the file on the net or email it to me 8please don't
attach a file to this NG)
email: frank[dot]kabel[at]freenet[dot]de


--
Regards
Frank Kabel
Frankfurt, Germany

I see, well the two document sheets are not exact. That's
why I'd like Excel to "look at the cells, see if there's a
match and return the "old contact" company name to replace
the "hidden columns" company name.

If I posted the document somewhere on the net, would
looking at it help?

sandy
-----Original Message-----
Hi
yes: to find a match firstname and lastname have to be identical.
The can be in different rows in sheet1 and sheet2 but they have to
be an exact match to be found

--
Regards
Frank Kabel
Frankfurt, Germany


Sandy wrote:
The result is #NA

Columns A and B in both worksheets are First Name and Last
Name.

Are you saying they need to have the exact same
information line by line?

thanks
-----Original Message-----
Hi
an what is your result?
- an error
- wrong data?

Column A and column B on both sheets have to store the same kind
of information


--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag [email protected]...
It still doesn't work. :-(

I have worksheet A ("Old Contacts") and worksheet B
("hidden columns"). Put your newest formula on worksheet B
("hidden columns") Cell D2.

thanks,
sandy
-----Original Message-----
Hi
sorry my fault. Try
=INDEX('Old Contacts'!$C$2:$C$10076,MATCH (A2&B2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'! $B$2:$B$10076,0))


--
Regards
Frank Kabel
Frankfurt, Germany


Sandy wrote:
Thank you Frank.

Here's how my formula looks:
=MATCH('Old Contacts'!$C$2:$C$10076,MATCH (A2&B2,'Old
Contacts'!$A$2:$A$10076 & 'Old Contacts'! $B$2:$B$10076,0))

Unfortunately, it's returning #NA

What am I doing wrong?

sandy
-----Original Message-----
Hi
assumptions:
- col. A: firstname
- col B: lastname
- col C: company name
- I assume that both lists are in the same workbook. Change
your references accodingly:

Enter the following array formula (entered with
CTRL+SHIFT+ENTER) in D1 on your worksheetB:

=MATCH('worksheetA'!$C$1:$C$1200,MATCH (A1&B1,'worksheetA'!
$A$1:$A$1200 & 'worksheetA'!$B$1:$B$1200,0))
and copy this down

--
Regards
Frank Kabel
Frankfurt, Germany


Sandy wrote:
I have two workbooks (A and B). I need to create a formula
in workbook B that will look up, compare and match
FirstName, LastName and Company in both wrbk A and B then
return the Company Name from wrbk A.

During a conversion 1,200 contacts were given the same
Company name. I have the old contacts with the correct
Company name and need to compare the two wrbks to automate
the look up and replace the wrong Company name in wrbk B
with the correct Company name from wrkb A

Is this possible?

.

.


.

.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top