Nested IF?
 

I searched in all the areas and I couldn't find this.

I have two spreadsheets. Spreadsheet A has addresses broken out in columns
F through I. The phone number is in column E. Spreadsheet B has addresses
broken out in columns C through F. The phone number is in column B.

Is there a way to do an IF function on spreadsheet B that says find the row
on spreadsheet A that has this phone number and check to make sure the
address cells match?

Nested IF?
 

On sheet2, use a helper column, say M

M2: =MATCH(B2,Sheet1!$E:$E,0)
N2:
=IF(ISNUMBER(M2),IF(AND(INDEX(Sheet1!$F:$F,M2)=C2, INDEX(Sheet1!$G:$G,M2)=D2,INDEX(Sheet1!$H:$H,M2)=E 2,INDEX(Sheet1!$I:$I,M2)=F2),"Match","No
match"))

copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Toria" wrote in message
...
I searched in all the areas and I couldn't find this.

I have two spreadsheets. Spreadsheet A has addresses broken out in
columns
F through I. The phone number is in column E. Spreadsheet B has
addresses
broken out in columns C through F. The phone number is in column B.

Is there a way to do an IF function on spreadsheet B that says find the
row
on spreadsheet A that has this phone number and check to make sure the
address cells match?

Nested IF?
 

Thanks, Bob! This worked wonderfully!!

"Bob Phillips" wrote:

On sheet2, use a helper column, say M

M2: =MATCH(B2,Sheet1!$E:$E,0)
N2:
=IF(ISNUMBER(M2),IF(AND(INDEX(Sheet1!$F:$F,M2)=C2, INDEX(Sheet1!$G:$G,M2)=D2,INDEX(Sheet1!$H:$H,M2)=E 2,INDEX(Sheet1!$I:$I,M2)=F2),"Match","No
match"))

copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Toria" wrote in message
...
I searched in all the areas and I couldn't find this.

I have two spreadsheets. Spreadsheet A has addresses broken out in
columns
F through I. The phone number is in column E. Spreadsheet B has
addresses
broken out in columns C through F. The phone number is in column B.

Is there a way to do an IF function on spreadsheet B that says find the
row
on spreadsheet A that has this phone number and check to make sure the
address cells match?

Nested IF?
 

"Bob Phillips" wrote...
On sheet2, use a helper column, say M

M2: =MATCH(B2,Sheet1!$E:$E,0)
N2: =IF(ISNUMBER(M2),IF(AND(INDEX(Sheet1!$F:$F,M2)=C2, INDEX(Sheet1!$G:$G,M2)=D2,
INDEX(Sheet1!$H:$H,M2)=E2,INDEX(Sheet1!$I:$I,M2)= F2),"Match","No match"))
....

FTHOI, a single formula alternative.

N2:
=IF(COUNT(M2),IF(COUNT(INDEX(1/(INDEX(Sheet1!$F:$I,M2,0)=C2:F2),
1,0))=4,"full","partial")
&" address","no phone #")&" match"