# interpretation for =INDEX(Sheet2!$B$2:$B$1214,MATCH(1,(Sheet2!$A$2:$A$1214=B2)*(Sheet2!$F$2:$F$1214=O2),0))

Hi can anyone please to interpreate the following

=INDEX(Sheet2!$B$2:$B$1214,MATCH(1,(Sheet2!$A$2:$A$1214=B2)*(Sheet2!$F$2:$F$1214=O2),0))

I have tried to understand this by looking at Index with Match functions.

Match(1,-------

Why is that first parameter 1 for Match() function?

## Answers

Looks like your problem is with the MATCH function so I will just explain on this. Let's first assume we have some data in the excel as below.

$A$1 = 2; $A$2 = 7

$B$1:$B$5 = {1, 2, 3, 4, 5}

$C$1:$C$5 = {6, 7, 8, 9, 0}

Now, we try to solve the equation below:

$B$1:$B$5 = $A$1

$C$1:$C$5 = $A$2

What excel does is it takes the value of $A$1 and compare it against $B$1, $B$2, and so on. The result will return as follow:

"$B$1:$B$5 = $A$1" = {FALSE, TRUE, FALSE, FALSE, FALSE}

"$C$1:$C$5 = $A$2" = {FALSE, TRUE, FALSE, FALSE, FALSE}

Then we do this..

($B$1:$B$5 = $A$1) * ($C$1:$C$5 = $A$2)

It will take 1 data from the same position in each of the result we got above, multiply it and return the result.

"{FALSE, TRUE, FALSE, FALSE, FALSE} * {FALSE, TRUE, FALSE, FALSE, FALSE}"

= {0, 1, 0, 0, 0}

So the MATCH function will have the input as follow..

MATCH(1, {0, 1, 0, 0, 0}, 0)

What this equation means is it will attempt to look for value "1" in the array, and return the position of the **FIRST** value that matched.

In our case, it will return 2.

Then this "2" is passed as row_num into INDEX function..

You can explain the rest by yourself.

Let's look at the formula starting at the components of the inner MATCH. For this I assume that this is an array formula.

As an array formula, Sheet2!$A$2:$A$1214=B2 gives a vector of TRUE and FALSE depending on whether the values in Sheet2!$A$2:$A$1214 equal B2. Similarly, Sheet2!$F$2:$F$1214=O2 gives a vector of TRUE and FALSE depending on whether the values in Sheet2!$F$2:$F$1214 equal O2.

Now, (Sheet2!$A$2:$A$1214=B2)*(Sheet2!$F$2:$F$1214=O2) multiplies two vectors of TRUE and FALSE row by row. Since TRUE*TRUE=1, but FALSE*TRUE=TRUE*FALSE=FALSE*FALSE=0, this gives a vector of 1 and 0 depending of whetter B2 equals Sheet2!$A$r and O2 equals Sheet2!$F$r *for the same row* r.

The MATCH(1, ...) then finds the first row r on Sheet2 where both B2 equals Sheet2!$A$r and O2 equals Sheet2!$F$r *for the same row* r.

Finally, the INDEX gives you the value in Sheet2!$B$2:$B$1214 for this row r.

### Need Your Help

### Pass subarray by reference (not by value) in Common Lisp

arrays multidimensional-array common-lisp pass-by-reference pass-by-pointer

Let's suppose I have an array - which I will call *my-array* - that looks like this: