Thanks Mr. Max,
This is working. But I want the returned value from sheet II i.e
MTM0337
MTM0418
i.e. the matched Invoice No. & does not matched Part No.
Is it possible.
--
Thanks,
Vikram P. Dhemare


"Max" wrote:

Perhaps something along these lines ..

Sample data as posted assumed in sheets: I, II, within A2:C13

In sheet: I

Put in D2, array-enter (CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(II!$A$2:$A$13=A2)*(II!$B$2:$ B$13=B2),0)),"",B2)
Copy D2 down to D13

The above returns in D9:D10

PTM0609
PTM0611

i.e. the 2 part nos in I
which do not match the invoice-part nos in II

In sheet: II

Put in D2, array-enter (CTRL+SHIFT+ENTER):
=IF(ISNUMBER(MATCH(1,(I!$A$2:$A$13=A2)*(I!$B$2:$B$ 13=B2),0)),"",B2)
Copy D2 down to D13

The above returns in D9:D10

MTM0337
MTM0418

i.e. the 2 part nos in II
which do not match the invoice-part nos in I
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vikram Dhemare" wrote in message
...
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$13,
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