CODEWITHSUNDEEP
excelexcel-formula
VJ4Testing
VJ4Testing

Reputation: 101

Compare two excel tables based on unique key using formula

I have two tables with same column names in excel which are getting data from two different sources based on certain calculations. We need to compare data between those two tables based on ID column value which will be provided by user in A2. I have attached snapshot of sample tables. I did try using sumproduct (as you can see in K2, but it doesn't work if any of the cell values has #NA as it's value. Please keep in mind that calculations update data for only a particular id (one row only for each table). in this example, values for only the rows with ID 200 will be updated for these two tables.

If I change value from #NA to an integer, formula (in K2) would work. Now I was even thinking about using if(And(logic1,logic2...)) to compare cell to cell values,but I don't kow how would I use it when the placement of ID's in columns C and G can vary and won't necessarly be in the same row.

Formula used:

=IF(G2:G5=A2,IF(SUMPRODUCT((G2:G5=A2)*(H2:I5))-SUMPRODUCT((C2:C5=A2)*(D2:E5))=0,"Match","No Match"),"")

enter image description here

Upvotes: 0

Views: 2802

Answers (2)

basic
basic

Reputation: 11968

Try following array formula:

=INDEX({"No Match","Match"},1,IFERROR(--(MATCH(COLUMNS($C$2:$E$2),MMULT(--(INDEX($C$2:$E$5,MATCH($A$2,$C$2:$C$5,0),0)=$G$2:$I$5),TRANSPOSE(COLUMN($C$2:$E$2)^0)),0)>0)+1,0))

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Upvotes: 0

jsteurer
jsteurer

Reputation: 244

This may help get you started.

=IF(AND(IF($A$2=G3,VLOOKUP(G3,C3:E6,2,0),"")=H3,IF($A$2=G3,VLOOKUP(G3,C3:E6,3,0),"")=I3),"Match","No Match")

enter image description here

Upvotes: 1

Related Questions