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")

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

"Bob" schrieb im Newsbeitrag
...
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")

Worked beautifully!

Thank you.

"Frank Kabel" wrote:

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

"Bob" schrieb im Newsbeitrag
...
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")