Do columns A and B of SheetB come from somewhere else then? Like your
Invoicing system, but SheetA is for payments received? How can you
ensure that the two lists are in the correct order? Say for example
that the 2nd and 3rd items were reversed in your payments list - if
you matched on the first one within SheetB then you would have a +ve
difference of 100, but the next one would be -100. I could give you a
method based on this, but as can be seen it wouldn't necessarily make
the correct matches.

I suppose a safer method would be to introduce a new column C in
SheetA and put this formula the

C2: =A2&TEXT(B2,"0.00")

and copy this down.

Then in SheetB you could enter this formula in C2:

=IF(ISNA(MATCH(A2&TEXT(B2,"0.00"),'Sheet A'!C$2:C$10000,0)),"not
present","matched")

and copy this down. This doesn't address your problem exactly, but
perhaps you can see how to adapt it.

Hope this helps.

Pete

On Nov 26, 9:35 pm, Jai wrote:
Hi,

I am trying to set up a vlookup formula to match two database. Following is
the example :

Sheet A List :
Col.A Col.B
Vendor amount
40694 2,154.51
40986 150.00
40986 250.00
125855 1,454.37
125855 2,272.96
166559 2,458.10
166559 16.81
166559 62.54
173204 4,611.53
173430 571.34

Sheet B List :
Col. A Col.B Col. C Col.D Diff (Col.B-Col.D)
40694 2,154.51 40694 2154.51 0
40986 150.00 40986 150.00 0
40986 250.00 40986 150.00 100
125855 1,454.37 125855 1454.37 0
125855 2,272.96 125855 1454.37 818.59
166559 2,458.10 166559 2458.10 0

Sheet A has all data for all vendors and amounts receivables. In sheet B,
all the data represents received amounts.
Please help me with following :
I need to match Sheet B with Sheet A for all the amounts received so Column
C and D shows the matched value and E column shows the difference in the
amounts.

In Sheet A, either I need to delete all the rows which has been matched with
Sheet B or need to set up a formula for all the outstanding amounts to be
received.

I am trying the Vlookup function and its returning to first match so there
are too many differences. As the lists are huge everymonth. I need to come up
with some formula or macro.

Thanks
Jai