Search code examples
arraysgoogle-sheetssyntaxgoogle-sheets-formulamatch

How does Match function treat an array of search key as input?

enter image description here

I have put formula =match($A$4:$A,$B$4:$B,0) in column C & =match($A4:$A,$B$4:$B,0) in Column D. The only difference between both is of a $.

In Column D Match formula search key $A4:$A is treated same as $A4 ($A4, $A5, $A6...) but in Column C Match formula search key $A$4:$A is not treated same as $A$4.

I understand absolute & relative cell references, but I really don't understand how come Column C & Column D have same output or in simple terms,
How does the same formula =match($A$4:$A,$B$4:$B,0) at different cells: C4, C5, C6, C7 & C8 give different outputs? How exactly the Match function treat an array of search key ($A$4:$A) as inputs?

Google Sheet Link

Solution

  • your formula is incomplete.

    you are using arrays/ranges within a MATCH but there should be ARRAYFORMULA/INDEX wrapping around MATCH to process it as an array. also, since this is an array the locking mechanism with $ is pointless

    use:

    =ARRAYFORMULA(IFNA(MATCH(A4:A, B4:B, 0)))

    enter image description here

    update:

    let's see your row 5 as an example... in C5 you have

    =MATCH($A$4:$A, $B$4:$B, 0)

    and in D5 you have:

    =MATCH($A5:$A, $B$4:$B, 0)

    what you need to realize is that both above formulae are exactly the same as this one:

    =MATCH(A5, B4:B, 0)

    why? let's analyze the above functionality... we are trying to find the exact position of A5 within range B4:B the result is 2 because A5 is found in B5 and that's the 2nd cell/position from range B4:B. this formula is syntax-wise correct and it is meant to be used in one single cell with the possibility of dragging it up or down.

    now let's analyze =MATCH($A5:$A, $B$4:$B, 0) - everything is the same because 1st argument of MATCH was not activated as array/range. in other words, as soon as you wrap the MATCH into ARAYFORMULA the 1s argument of MATCH (which is $A5:$A) gets activated by ARRAYFORMULA wrapping and it gets recognized as an actual valid range. without ARRAYFORMULA wrapping $A5:$A is recognized only as A5 despite it being written as a multi-cell range. eg MATCH is able to read it only as A5

    at this point you may say "ok, so far it makes sense but how about when the 1st argument is A4:A why the behavior is same?" let's take a look... first we need to agree that A4:A is the same as

    $A$4:$A
A$4:A
$A4:$A
$A$4:A
A4:$A
A$4:$A
INDIRECT("A4:A")

    the lock $ we may or may not use to lock the column, row, or both or none - does not matter. the lock $ is useful only when we are about to drag the formula down, up, or to the sides so we could keep the intended window of reference

    so for simplicity, we are left with A4:A but since the formula =MATCH($A4:$A, $B$4:$B, 0) sits in row 5, the range A4:A is understood as it being as A5. the row on which the formula is located has higher priority over range like A4:A because MATCH recognizes it that A4:A was not activated as a valid range from some "outside force" (ARRAYFORMMULA) so even if you write it as A4:A the MATCH formula sees only the row it sits on and nothing beyond.

    therefore even =MATCH($A$4:$A, $B$4:$B, 0) is exactly same as =MATCH(A5, B4:B, 0)