Formula for comparing text in two workbooks

[Guest]

I have a wkbk with first names, last names, street. Another workbook has
first names, last names. I would like to compare the two and if both columns
match, I need to insert the street.
I have the following to tell me if there is a match
=IF(SUMPRODUCT(('[!ID.xls]all'!$B$2:$B$541=E11)*('[!ID.xls]all'!$C$2:$C$541=K11)), "Match", "Need to ID")

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=IF(SUMPRODUCT(('[!ID.xls]all'!$B$2:$B$541=E11)*('[!ID.xls]all'!$C$2:$C
$541=K11)),INDEX('[!ID.xls]all'!$A$2:$A$541,MATCH(1,('[!ID.xls]all'!$B$
2:$B$541=E11)*('[!ID.xls]all'!$C$2:$C$541=K11),0)), "Need to ID")


--
Regards
Frank Kabel
Frankfurt, Germany

I have a wkbk with first names, last names, street. Another workbook has
first names, last names. I would like to compare the two and if both columns
match, I need to insert the street.
I have the following to tell me if there is a match:

=IF(SUMPRODUCT(('[!ID.xls]all'!$B$2:$B$541=E11)*('[!ID.xls]all'!$C$2:$C
$541=K11)), "Match", "Need to ID")

 

[Guest]

Worked beautifully!

Thank you.

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=IF(SUMPRODUCT(('[!ID.xls]all'!$B$2:$B$541=E11)*('[!ID.xls]all'!$C$2:$C
$541=K11)),INDEX('[!ID.xls]all'!$A$2:$A$541,MATCH(1,('[!ID.xls]all'!$B$
2:$B$541=E11)*('[!ID.xls]all'!$C$2:$C$541=K11),0)), "Need to ID")


--
Regards
Frank Kabel
Frankfurt, Germany

I have a wkbk with first names, last names, street. Another workbook has
first names, last names. I would like to compare the two and if both columns
match, I need to insert the street.
I have the following to tell me if there is a match:

=IF(SUMPRODUCT(('[!ID.xls]all'!$B$2:$B$541=E11)*('[!ID.xls]all'!$C$2:$C
$541=K11)), "Match", "Need to ID")