הבלוג של מני: This is C-r-a-z-y: SQRT instead of MATCH

יום שבת, 24 בדצמבר 2022

This is C-r-a-z-y: SQRT instead of MATCH

This is C-r-a-z-y: SQRT instead of MATCH

I reckon you're all familiar with INDEX-MATCH. There's a myriad of video clips on Youtube with good, detailed explanations.

What I'm going to talk about is the: INDEX-MATCH-MATCH combination, where your search is two-dimensional: row (vertical) and column (horizontal).

So, actually you're looking for a solution which fulfills 2 conditions (criteria),
as demonstrated in the following pictures.

Example 1: How many shifts did David have in the month of December?

Pic 1: INDEX-MATCH-MATCH - Example 1

Example 2: What subject does Jim teach in the sixth Grade?

Pic 2: INDEX-MATCH-MATCH - Example 2

Example 3: What's Lara's score in Sports?

Pic 3: INDEX-MATCH-MATCH - Example 3

Now, let's move to a more complicated challenge. Here, instead of 2 conditions, one vertical (row) and one horizontal (column) we have 2 horizontal conditions.

Consider the following dataset:
We are looking for the SKU by Song and composer. Actually, we are looking for the row in which these two meet.

But here the INDEX-MATCH-MATCH doesn't work.

Pic 4: INDEX-MATCH-MATCH - does not work

One possible solution is using the filter function, as can be seen in the picture below:

Pic 5: Using FILTER

But, a small change to the original INDEX-MATCH-MATCH will make it operative and functioning: We add the SQRT function, and we also multiply both MATCH parts of the formula:

Pic 6: Adding SQRT to INDEX-MATCH-MATCH – solves the problem

Amazing, but how does it work? Can you please explain?

How does it work?

Since we're looking for an SKU where the Song and the Composer are on the same row, then each of the two MATCH-es will yield the same number (i.e., row number).

So, the multiplication of a number by itself gives us its square value.

What is left for us to do is to SQRT this number in order to find the row
So the solution is: INDEX(SQRT(MATCH(..)*MATCH(..)),)

Pic 7: Adding SQRT to INDEX-MATCH-MATCH - Explanation

Now, let's take a more complex situation where a song appears more than once.

With FILTER, the solution is very simple.

Pic 8: A more complex scenario - solved by FILTER

Can this scenario be also solved with the previous method of
SQRT(MATCH(…)*MATCH(…))?

Well, not exactly, but basically: yes.

As I said, each composer and each song might appear more than once.

For example, "Garden" appears twice: in row 5 and in row 6,

so A3:A10=E3, will return the first occurrence (5) where we need the sixth occurrence (Garden with Keith Jarret, not: Garden with Shania Twain)
So we have to adapt our solution to the new situation.

And here's the solution to the more complex situation, still using
SQRT(MATCH(...)*MATCH(…))