A question about VLOOKUP / any other helpful formula
 

Hi All, i am new in this group, and looks like you have perfect
knowledge in Excel.

Now, i have to recocile between two systems, first one will send
generate a payment batch lets call this system (A), and the other one
will recieve this batch and process it, let call this one (B), my role
is to reconcile and check if system (B) didn't process the amounts sent
by system (A), usually i get a copy of the LOG from sys (A) + sys (B)
and import them into excel and do the job as the following:

(A) (B)
1) Sami 1,0000 Sami 1,0000
2) John 1,500 John 1,500
3) Sara 1,000 Sara 1,000

In my case i always use VLOOKUP (B1,A1:A3,FALSE,1), but sometimes it
wont work specially when i reconcile between 2000 accounts and some of
the records in colume (B) is are missing becuase it got dropped somehow
while the batch sent in the begining from sys (A) -- sys (B) and that
result #/N/A in all the accounts after the missing record!

is there a way that excel can generate #N/A on that particular missing
record ONLY or can we use other formula to do that. Really appreciate
your help :)

A question about VLOOKUP / any other helpful formula
 

If I understand correctly, you could use an IF formula. The following
displays "Found It" if the lookup value is found. In your case, remove
"Found It" and so that just the "" remain and the formula will return
#N/A when the value is not found.

=IF(VLOOKUP(A1,$B$1:$B$3,1,FALSE)<"#N/A","Found It")

- John Michl

A question about VLOOKUP / any other helpful formula
 

Thanks a lot i really appreciate your prompt reply, but i am still
getting the same result, i mean when:
(A) (B)
1) Sami 1,0000 Sami 1,0000 Found it
2) John 2,000 Sara 1,000 #N/A
3) George 200 John 2,000 #N/A
4) Seven 300 George 200 #N/A

Becuase Sara is missing in colume (A) that result #N/A from Row 2 and
after, that is exactly my case when i work on 2000 rows, when a record
is missing on colume (A) it will affect our formula.

A question about VLOOKUP / any other helpful formula
 

I apologize John, it works perfect %100 but i just changed the order of
selection
Instead of =IF(VLOOKUP(A1,$B$1:$B$3,1,FALSE)<"#N/A","Found It")
^^^^^^^^^^^^^^^^


I used =IF(VLOOKUP(B1,$A$1:$A$3,1,FALSE)<"#N/A","Found It")
^^^^^^^^^^^^^^^^^

Thanks again for your kind help :)