MATCH function for multiple cells-CodePudding

I have a formula as below, which checks two cells for a MATCH with Datatable1 (assigned a Name with some data). Here if either of one cell matches with Datatable1, then it writes the value in I7.

IF(OR((ISNUMBER(MATCH($L7;DataTable1;0))=TRUE);(ISNUMBER(MATCH($O7;DataTable1;0))=TRUE));$I7;"")

Is it possible to condense the formula with single MATCH function and check $L7 or $O7 matches with Datatable1? Only reason is to shorten the formula.

CodePudding user response：

You could use an array formula combining COUNTIF and SUM to check if the value exists in Datatable1. Something like this should work:

=SUM(COUNTIF(DataTable1;$L7);COUNTIF(DataTable1;$O7))

This will return 0 if there is no coincidence, and will return 1 or 2 if there is 1 or 2 coincidences. Then you just need to wrap this into your IF:

=IF(SUM(COUNTIF(DataTable1;$L7);COUNTIF(DataTable1;$O7))=0;"";$I7)

If there's no match, return nothing, any case return value at I7

It's more condensed, it's a shorter formula.

EDIT: After looking @ScottCraner you can add CHOOSE and make it shorter, but as an array formula:

=IF(SUM(COUNTIF(DataTable1;CHOOSE({1;2};$L7;$O7)))=0;"";$I7)

Because it's an array formula, it must be entered pressing CTRL ENTER SHIFT

CodePudding user response：

You could use:

=IF(SUMPRODUCT((L7:O7=DataTable1)*{1,0,0,1}),I7;"")

If you want to use a single MATCH() you could try:

=IF(MOD(IFERROR(MATCH(DataTable1,L7:O7,0),3),4)<2,I7,"")