Nested IF?

[Toria]

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?

 

[Bob Phillips]

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

copy down

--
HTH

Bob

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

 

[Toria]

Thanks, Bob! This worked wonderfully!!

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

copy down

--
HTH

Bob

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

 

[Harlan Grove]

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"