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