Glad you liked them. As I said, changing the structure would make things much easier, but sometimes we work w/ what we have.
In short, each formula is looking at the table and finding the non-blank cells. For the Dates, we need to know which column(s) or row(s) our data is in (hence the COLUMN/ROW functions). Once we have that array of numbers, we use either the SMALL or COUNTIF function to tell the formula which number from the array to choose. After we have the row/col number, we plug that into INDEX, who returns the correct value.
One small problem at this point. If you do:
=INDEX(Range,0)
The INDEX
doesn't break. It just assumes you want the first value. But in our case, if there's no match, we want to make an error so we can trap it w/ the IFERROR function. To do this, we use the math equation:
(1/x)^-1
If you remember you're algebra, you'll know that that equation doesn't really do anything math-wise, it evaluates to x. But...if x = 0, then we get a DIV/0 error...and we can
trap it!!!
I used the "|" marker as a form of delimiter. Let's say we wanted to find the combination of "Hot" with "Dog Snacks". But, in our data, we had a couple of cells with "Hot Dog" and "Snacks". If we just do a normal concatenation, our formula might think this was a match.
So, we stick the delimiter in there, so it instead compares:
Hot|Dog Snacks
with
Hot Dog|Snacks
and XL knows that this isn't a match now. It might not have been needed in this case, but I tend to try to error proof things where I can.
