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:

webservice problem

vb.net web-services

hey all i have a problem in webservice that i am consuming. it takes a string parameter which if i sent empty it does not return anything but if i check the output in the soap ui by removing this s...