CODEWITHSUNDEEP
excelexcel-formulavlookup
anoop ramachandran
anoop ramachandran

Reputation: 19

Compare two tables in excel across different rows

I have an excel sheet with below data:

    A   | B     |    C  |    D   |   E   |   F   |   G   
----------------------------------------------------------
Apple   |Orange |Mango  |        |1      |2      |3
Coffee  |Tea    |Juice  |        |Apple  |Orange |Mango
1       |2      |3      |        |Rose   |Jasmine|Sun Flower

Columns ABCD and EFG are two different tables. I want to compare A to E, B to F and C to G but the problem is they will not be in the same order and there will be instances where it will not match as well. I want to populate column D with "Exists" or "Not Exists" as part of table ABCD and say the same combination exists in EFG table. For example, for row number 1 and 3, it should say exists as these values exist in the second table in same order. All 3 columns need to match.

I tried creating a VLOOKUP using multiple references for the same but could not get it to work as its my first time. Any help is appreciated.

Thanks in advance

Upvotes: 0

Views: 862

Answers (1)

BigBen
BigBen

Reputation: 50007

Use COUNTIFS and check if the result is >0.

=IF(COUNTIFS(E:E,A1,F:F,B1,G:G,C1)>0,"Exists","Not Exists")

enter image description here

Upvotes: 0

Related Questions