Multiple Match Function Returns Error Against Table with One Row

Question

The following multi-match array function fails against a table with only 1 row. However it works as as soon as there is another row added.

{=MATCH("A"&"C",myTable[Col1]&myTable[Col3],0)}

curly brackets added to show array formula

See picture as example

Is there anyway to force this to work for at times my real data may only have 1 row in the table? (And this also works with only one row of data not explicity formatted as a table).

This modification doesn't work either:

=MATCH("A"&"C",myTable[[#Data],[Col1]]&myTable[[#Data],[Col3]],0)

This answer seems related: stackoverflow.com/a/29671342/6651557 — Jacob Edmond
– Jacob Edmond, Commented Mar 23, 2017 at 19:34

Scott Craner · Accepted Answer · 2017-03-23 19:29:48Z

2

I do not know why but, you can avoid it by using Aggregate:

=AGGREGATE(15,6,(ROW(Table1[Col1])-MIN(ROW(Table1[Col1]))+1)/((Table1[Col1]="A")*(Table1[Col3]="C")),1)

answered Mar 23, 2017 at 19:29

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Scott Holtzman Over a year ago

Thanks Scott. +1 for providing working solution. My only issue is readability at this point. See my update about the modification I found that does work (and seems a little less messy and understandable than AGGREGATE.

Scott Craner Over a year ago

@ScottHoltzman I guess it is a matter of opinion. The Aggregate, when using array formulas, is easier to understand than others. As it has the conditions spelled out.

Scott Holtzman Over a year ago

Fair point. I'll admit I am not 100% comfortable / experienced with it either, so that may skew my viewpoint. For instance, I don't really understand how this part (ROW(Table1[Col1])-MIN(ROW(Table1[Col1]))+1)/((Table1[Col1]="A")*(Table1[Col3]="C")) goes with the SMALL function.

Scott Holtzman Over a year ago

If you have time and a little patience, can you shed some light to lead me in right direction?

Scott Craner Over a year ago

@ScottHoltzman It is building an array of row numbers and #DIV/0 errors. The (ROW(Table1[Col1])-MIN(ROW(Table1[Col1]))+1) makes sure that the first row in the table is returning 1, if we were not using tables I would have only done the ROW() as I would use the full column in index so: =INDEX(C:C,AGGREGATE(15,6,ROW(C6)/... so I do not need to do the rest of the math. (continued)

|

Scott Holtzman · Accepted Answer · 2017-03-24 14:48:16Z

2

This modification works, for an alternative to @ScottCraner's answer.

{=MATCH("A"&"C",myTable[[#All],[Col1]]&myTable[[#All],[Col3]],0)-1}

Curly brackets added to designate this needs to be entered as an array formula (by pressing Ctrl+Shift+Enter

edited Mar 24, 2017 at 14:48

answered Mar 23, 2017 at 20:16

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Comments

Jacob Edmond · Accepted Answer · 2017-03-23 20:15:04Z

0

If you prefer, it seems you can just wrap your original formula in an iferror:

{=IFERROR(MATCH("A"&"C",myTable[Col1]&myTable[Col3],0),IF("A"&"C"=myTable[Col1]&myTable[Col3],1,0))}

This also gives you the opportunity to build in a return value in the case that you do not have a match in the table at all.

answered Mar 23, 2017 at 20:15

Jacob Edmond

8082 gold badges7 silver badges14 bronze badges

Collectives™ on Stack Overflow

Multiple Match Function Returns Error Against Table with One Row

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related