Hi Mr. Max,

It is working. But the problem is that table II does not have the whole Inv.
No. or whole part nos.
There may have some missing entries which i have to find out. The total data
of table I is about 40000 rows & the data of table II is around 380000 rows
that means almost 2000 invoices or part nos. may have missing.
After certain rows the formula returns the 0 value instead the result.
What I want to reconcile is the sales data of "X" company against the
purchase of "Y" company (X is the supplier of Y company)
Is it possible cause the earlier formula has got limitations.
--
Thanks,
Vikram P. Dhemare


"Vikram Dhemare" wrote:

Hi friends,
I have two different sheets with two different tables. The common unique
value in these tables are Invoice No. & Part No. Now I wish to reconcile the
data for missing value or wrong value from each other table like : maching
the value of col. Invoice No. but at the same time does not matching the
value of col. Part No. of that particular value. I have tried this by using
Index match & offset array function.
{=IF(ISNA(INDEX(II!$A$1:$C$13,MATCH(I!A3&I!B3,II!$ A$1:$A$13&II!$B$1:$B$13,0),2)),OFFSET(INDEX(II!$A$ 1:$C$13,MATCH(I!A3&I!B3,II!$A$1:$A$13&II!$B$1:$B$1 3,0),1),0,1),"")} For your ready ref. I am providing the tables.

Invoice No. Part No. Qty Result
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 PTM0609 70 #N/A (the returned value should have been
MTM0337)
61118142 PTM0611 70 #N/A
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70
And the table of sheet II contents :
Invoice No. Part No. Qty
61118138 MTM0213 155
61118138 MTM0214 115
61118138 MTM0295 2000
61118138 MTM0408 155
61118141 ATM0237 32
61118141 ATM0273 32
61118141 MTM0117 216
61118141 MTM0337 70
61118142 MTM0418 70
61118142 PTM0613 70
61118142 PTM0615 70
61118142 PTM0617 70

Could you help me out by resolving this:

--
Thanks,
Vikram P. Dhemare