In the example below I am trying to determine the rows that contain duplicate
account numbers. I am thinking that if I could do an IF/AND function I could
get the information I want. The second column gives me the results I want, I
just can't get there. Basically if an account in column one is also located
in column 3 and column 4 is a "91" I would like a return of no. If an
account in column 1 is in column 3 and column 4 is a "92" I would like a
return of yes. If an account in column 1 is not in column two, regardless of
what is in column 4, I would like a return of "single". Is this possible.
I have been completely unsuccessful

acct 1 acct 2 tied ind
123456 no 978564 91
546464 no 97974649 91
9849464 no 974649 91
46464 no 657987 91
657987 yes 46464 92
974649 yes 9849464 92
97974649 yes 546464 92
978564 yes 123456 92
1111111 single 2222222 92

=IF(ISNA(VLOOKUP(A2,$C$2:$C$9,1,FALSE)),"single",I F(D2=91,"no","yes"))

Fill down as far as needed

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"fluffy" wrote in message
...
In the example below I am trying to determine the rows that contain
duplicate
account numbers. I am thinking that if I could do an IF/AND function I
could
get the information I want. The second column gives me the results I
want, I
just can't get there. Basically if an account in column one is also
located
in column 3 and column 4 is a "91" I would like a return of no. If an
account in column 1 is in column 3 and column 4 is a "92" I would like a
return of yes. If an account in column 1 is not in column two, regardless
of
what is in column 4, I would like a return of "single". Is this
possible.
I have been completely unsuccessful

acct 1 acct 2 tied ind
123456 no 978564 91
546464 no 97974649 91
9849464 no 974649 91
46464 no 657987 91
657987 yes 46464 92
974649 yes 9849464 92
97974649 yes 546464 92
978564 yes 123456 92
1111111 single 2222222 92

this works wonderfully. Thank you so much. You have saved what is left of
my hair.

"Niek Otten" wrote:

=IF(ISNA(VLOOKUP(A2,$C$2:$C$9,1,FALSE)),"single",I F(D2=91,"no","yes"))

Fill down as far as needed

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"fluffy" wrote in message
...
In the example below I am trying to determine the rows that contain
duplicate
account numbers. I am thinking that if I could do an IF/AND function I
could
get the information I want. The second column gives me the results I
want, I
just can't get there. Basically if an account in column one is also
located
in column 3 and column 4 is a "91" I would like a return of no. If an
account in column 1 is in column 3 and column 4 is a "92" I would like a
return of yes. If an account in column 1 is not in column two, regardless
of
what is in column 4, I would like a return of "single". Is this
possible.
I have been completely unsuccessful

acct 1 acct 2 tied ind
123456 no 978564 91
546464 no 97974649 91
9849464 no 974649 91
46464 no 657987 91
657987 yes 46464 92
974649 yes 9849464 92
97974649 yes 546464 92
978564 yes 123456 92
1111111 single 2222222 92