CODEWITHSUNDEEP
excel-formulagoogle-sheetsformula
Liu Kang
Liu Kang

Reputation: 1389

Match value and count corresponding value differences between two sheets

I have two Google sheets with different Fictional characters (column A) and their corresponding Type (column B).

Sheet1

| Fictional character | Type            |
|:--------------------|:---------------:|
| Spider Ham          | Pig             |
| Iron Man            | Human           |
| Captain America     | Human           |
| Thor                | God             |
| Hercules            | God             |
| Superman            | Alien           |

Sheet2

| Fictional character | Type            |
|:--------------------|:---------------:|
| Spider Ham          | Human           |
| Iron Man            | Human           |
| Captain America     | Human           |
| Thor                | God             |
| Hercules            | Demi-God        |
| Doctor Octopus      | Human           |

The differences between Sheet1 and Sheet2:

I want to compare how many of the fictional characters that have a different Type (excluding characters that does not exist in both sheets)

Sheet3 should look like this:

| Difference          | Sum             |
|:--------------------|----------------:|
| Same type           | 3               |
| Not the same type   | 2               |

I have tried with the following, but it's not a valid formula - and I haven't figured out how to exclude if characters that does not exist in both sheets:

=COUNTIF(VLOOKUP(Sheet1!A:A;Sheet1!A:B;2;FALSE)=VLOOKUP(Sheet2!A:A;Sheet1!A:B;2;FALSE);"=TRUE") =COUNTIF(VLOOKUP(Sheet1!A:A;Sheet1!A:B;2;FALSE)=VLOOKUP(Sheet2!A:A;Sheet1!A:B;2;FALSE);"=FALSE")

Upvotes: 1

Views: 88

Answers (1)

TheMaster
TheMaster

Reputation: 50575

Same Type: 

=SUMPRODUCT(IFERROR(VLOOKUP(A2:A7,A11:B16,2,0)=B2:B7,0))

Not the same type: 

=SUMPRODUCT(IFERROR(VLOOKUP(A2:A7,A11:B16,2,0)<>B2:B7,0))

Notes:
A2:B7=Sheet 1 data
A11:B16=Sheet 2 data
Change , to ;

Upvotes: 1

Related Questions