Matching Records

[Freshman]

Dear experts,

I have a table which column A is for account numbers of new transactions to
be input from time to time. Column E listed all existing account numbers. So,
my question is, if I input/import an account number 12345 in A2, 45678 in A3
etc. and 12345 is a new account number and 45678 is our existing account
number, then D2 will remain blank and D3 will show "Y". Is it possible? If
yes, please advise how to do it.

Thanks in advance.

 

[Per Jessen]

Hi

Place this formula in D2:

=IF(ISERROR(LOOKUP(A2;$E$1:$E$2));"";"Y")

Change range $E$1:$E$2 in the formula to refere to the list of existing
account numbers, then copy the formula down to the range needed.

Regards,

Per

 

[Mac]

Dear Freshman

If I'm understanding your questing correctly and column E contains the
numbers of all existing accounts the following might be useful:

=IF( NOT( ISNA(MATCH(A2,$E$1:$E$100,0))), "Y", "")

Place this formula in column D

 

[Mac]

Dear Freshman

If I'm understanding your questing correctly and column E contains the
numbers of all existing accounts the following might be useful:

=IF( NOT( ISNA(MATCH(A2,$E$1:$E$100,0))), "Y", "")

Place this formula in column D

 

[Ivyleaf]

Hi,

I think it's worth mentioning that Mac's approach will work regardless
of what order column E is in, while Per's will require column E to be
sorted in an ascending order.

Also, it looks like someone has switched Per's ';' and ',' keys...
either that or my regional settings are up the creek!

Cheers,
Ivan.

 

[Per Jessen]

Hi Ivan

My regional setteings are Danish, so it works where I am ;-)

Snip...

Also, it looks like someone has switched Per's ';' and ',' keys...
either that or my regional settings are up the creek!

Best regards,
Per

 

[Ivyleaf]

Hi Per,

Lol... My apologies

Ivan.

Hi Ivan

My regional setteings are Danish, so it works where I am ;-)

Snip...




Best regards,
Per

 

[Freshman]

Thanks Per. Thanks for your help.

Hi

Place this formula in D2:

=IF(ISERROR(LOOKUP(A2;$E$1:$E$2));"";"Y")

Change range $E$1:$E$2 in the formula to refere to the list of existing
account numbers, then copy the formula down to the range needed.

Regards,

Per

 

[Freshman]

Hi Mac,

Your function is working well. Thanks a million.