Match function without a look up value

[myespooir]

Hi there,

I am trying to understand the formula here. The function of the formula is to look up unique values from a column. The match function here has no look up value. Could anyone explain it a little to me? Does there have sth to do with the fact that it is an array formula?

={IF($D$12="","",IF($D$12="Class",OFFSET('Sheet1'!$A$1,MATCH(,COUNTIF(C$16:C16,'Sheet1'!$K$7:$K$2000),),)&"",OFFSET('Sheet1'!$B$1,MATCH(,COUNTIF(C$16:C16,''Sheet1'!$L$7:$L$2000),),)&""))}

Thanks a lot!
Myespooir

 

[Marcelo Branco]

Welcome to Mr Excel

It's not a good practice omit arguments! When the argument is omitted Excel assumes as being equal to 0 (zero)

So,in fact, the formula is
...OFFSET('Sheet1'!$A$1,MATCH(0,COUNTIF(C$16:C16,'Sheet1'!$K$7:$K$2000),0),0)...


M.

 

[myespooir]

Hi M,

Thanks a lot for the reply. I would like to ask a follow up question.
In the formula, if the look up value is 0, does it mean that the match formula looks up for 0s in the data?

Thanks,
Myespooir

 

[Marcelo Branco]

Yes

M.