Advent of Code 2015 - Day 16: Aunt Sue

Part 1

Description

Your Aunt Sue has given you a wonderful gift, and you'd like to send her a thank you card. However, there's a small problem: she signed it "From, Aunt Sue".
You have 500 Aunts named "Sue".
So, to avoid sending the card to the wrong person, you need to figure out which Aunt Sue (which you conveniently number 1 to 500, for sanity) gave you the gift. You open the present and, as luck would have it, good ol' Aunt Sue got you a My First Crime Scene Analysis Machine! Just what you wanted. Or needed, as the case may be.
The My First Crime Scene Analysis Machine (MFCSAM for short) can detect a few specific compounds in a given sample, as well as how many distinct kinds of those compounds there are. According to the instructions, these are what the MFCSAM can detect:

• children, by human DNA age analysis.
• cats. It doesn't differentiate individual breeds.
• Several seemingly random breeds of dog: samoyeds, pomeranians, akitas, and vizslas.
• goldfish. No other kinds of fish.
• trees, all in one group.
• cars, presumably by exhaust or gasoline or something.
• perfumes, which is handy, since many of your Aunts Sue wear a few kinds.

In fact, many of your Aunts Sue have many of these. You put the wrapping from the gift into the MFCSAM. It beeps inquisitively at you a few times and then prints out a message on ticker tape:

children: 3
cats: 7
samoyeds: 2
pomeranians: 3
akitas: 0
vizslas: 0
goldfish: 5
trees: 3
cars: 2
perfumes: 1

You make a list of the things you can remember about each Aunt Sue. Things missing from your list aren't zero - you simply don't remember the value.
What is the number of the Sue that got you the gift?

Input

Solution

This problem can be solved without VBA and only using formulas. Here is my Excel sheet:

 

In A3:A12, are the detectable compounds. Detected values from sample are in the next column (B3:B12).I put my split input in E3:K502. My array formula in M3, for example, is

{=SUMPRODUCT(IF(F3=$A$3:$A$12,1,0),IF(G3=$B$3:$B$12,1,0))}

While in P3, i just simply added up the values in M3:O3. Next, to find the right Aunt Sue, I just had to find the one whose sum is 3.

Part 2

Description

As you're about to send the thank you note, something in the MFCSAM's instructions catches your eye. Apparently, it has an outdated retroencabulator, and so the output from the machine isn't exact values - some of them indicate ranges.
In particular, the cats and trees readings indicates that there are greater than that many (due to the unpredictable nuclear decay of cat dander and tree pollen), while the pomeranians and goldfish readings indicate that there are fewer than that many (due to the modial interaction of magnetoreluctance).
What is the number of the real Aunt Sue?

Solution

For the part 2, I just needed to add these operator nest to the detected values: > next to cats and trees, < next to pomeranians and goldfish. Then, modifying my formula into:

{=SUMPRODUCT(IF(F3=$A$3:$A$12,1,0),IF(NOT(ISERROR(FIND($C$3:$C$12,MID("<=>",2+SIGN(G3-$B$3:$B$12),1),1))),1,0))}

I get my expected result.