NOTFOUND

[schardt679]

NOTFOUND returns TRUE or FALSE if a cell in a given range does not contain a value.

Search can made case sensitive but isn't by default.
Other functions on minisheet: AFORMULATEXT.

=NOTFOUND(Array☛ range to check if cells don't contain a value, Search_Value☛ value to look for in array, [Case_Sensitive]☛ 1=search is case sensitive; 0/=is not)
⁂[]=optional; =default; =omit

=LAMBDA(Array,Search_Value,[Case_Sensitive],
      LET(Arr, Array,          Val, Search_Value,          Case, Case_Sensitive,
             ArrScan, ISOMITTED(Arr),          ArrMSG, "Array required",
             ValScan, ISOMITTED(Val),          ValMSG, "Search_Value required",
             CaseScan, AND(Case<>{1,0}),          CaseMSG, "Case=1,0",
             ERRORS, IFS(ArrScan, ArrMSG,  ValScan, ValMSG,  CaseScan, CaseMSG,  1, 0),
             SearchCase, SEARCH(Val, Arr),          FindCase, FIND(Val, Arr),
             Result, --(ISNUMBER(IF(Case, FindCase,  SearchCase)))=0,
             Return, IF(ERRORS<>0, ERRORS,  Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J
1		NOTFOUND
2
3		Original Data			Search_Value	Case_Sensitive		Result
4		My name is Patrick.	I went to CalU of PA.			0		FALSE	FALSE
5			#N/A					TRUE	TRUE
6		I have 3 degrees.	I am 28.					FALSE	FALSE
7		I graduated in:	2017					FALSE	TRUE
8
9		Search_Value is " "
10		Not Case Sensitive
11		Formula in cell H4☛ =NOTFOUND(B4:C7, E4, F4)
12
13
14
15		Original Data			Search_Value	Case_Sensitive		Result
16		Alex			M	1		TRUE
17		Carter						TRUE
18		Paula						TRUE
19		Lydia						TRUE
20		Mary M						FALSE
21		Mary Mc						FALSE
22		Mike						FALSE
23		Shelly						TRUE
24		Taylor						TRUE
25		William						TRUE
26
27		Search_Value is "M"
28		Case Sensitive
29		Formula in cell H16☛ =NOTFOUND(B16:B25, E16, F16)
30
31
32
33		Original Data			Search_Value	Case_Sensitive		Result
34		Alex			M	0		TRUE
35		Carter						TRUE
36		Paula						TRUE
37		Lydia						TRUE
38		Mary M						FALSE
39		Mary Mc						FALSE
40		Mike						FALSE
41		Shelly						TRUE
42		Taylor						TRUE
43		William						FALSE
44
45		Search_Value is "M"
46		Not Case Sensitive
47		Formula in cell H34☛ =NOTFOUND(B34:B43, E34, F34)
48
49
50
51		Original Data			Search_Value	Case_Sensitive		Result
52		(164) 507-0693	(717) 361-2326		25			TRUE	TRUE
53		(193) 593-0165	(718) 984-1786					TRUE	TRUE
54		(278) 525-2728	(780) 530-9377					FALSE	TRUE
55		(392) 325-8368	(828) 702-9706					FALSE	TRUE
56		(452) 762-9224	(914) 877-8008					TRUE	TRUE
57		(466) 956-7649	(917) 675-2878					TRUE	TRUE
58		(491) 862-9999	(926) 506-1689					TRUE	TRUE
59		(501) 993-9565	(982) 419-5562					TRUE	TRUE
60		(545) 216-3867	(986) 334-2317					TRUE	TRUE
61		(638) 105-4749	(992) 475-8319					TRUE	TRUE
62
63		Search_Value is "25"
64		Not Case Sensitive
65		Formula in cell H52☛ =NOTFOUND(B52:C61, E52, F52)
66
NOTFOUND

Cell Formulas
Range	Formula
H4:I7	H4	=NOTFOUND(B4:C7, E4, F4)
B11	B11	=AFORMULATEXT(H4)
H16:H25,H34:H43	H16	=NOTFOUND(B16:B25, E16, F16)
B29,B65,B47	B29	=AFORMULATEXT(H16)
H52:I61	H52	=NOTFOUND(B52:C61, E52, F52)
Dynamic array formulas.

 

[schardt679]

On second thought, I eliminated the error checking for the required values. Excel formulas already return errors if you are missing required arguments. This should speed of calculation time.

=LAMBDA(Array,Search_Value,[Case_Sensitive],
      LET(Arr, Array,          Val, Search_Value,          Case, Case_Sensitive,
             CaseScan, AND(Case<>{1,0}),          CaseMSG, "Case=1,0",
             SearchCase, SEARCH(Val, Arr),          FindCase, FIND(Val, Arr),
             Result, --(ISNUMBER(IF(Case, FindCase,  SearchCase)))=0,
             Return, IF(CaseScan, CaseMSG,  Result),
             Return
      )
  )

 

[schardt679]

This has been updated again. It now has 3 match types (fuzzy/ partial --> 0, from left/ start --> 1, & exact --> 2 with each type having the ability to be case sensitive. Currently this only supports one search value. The array can be 1 dimensional or 2 dimensional.

I am looking to improve this in two ways:
1. Allow for multiple search values
2. Shorten the code

I know someone on here will be able to find a solution that does both of those things. Any help would be greatly appreciated.

= LAMBDA(Array, Search_Value, [Match_Type], [Case_Sensitive],
    LET(
        Arr, Array,
        Val, Search_Value,
        Type, IFERROR(--(Match_Type), 3),
        Case, IFERROR(--(Case_Sensitive), 2),
        TypeScan, OR(Type < 0, Type > 2),
        TypeMSG, "Match_Type=0,1,2",
        CaseScan, AND(Case <> {0, 1}),
        CaseMSG, "Case_Sensitive=0,1",
        ERRORS, IFS(TypeScan, TypeMSG, CaseScan, CaseMSG, 1, 0),
        FuzzCS, FIND(Val, Arr),
        FuzzNCS, SEARCH(Val, Arr),
        LeftChar, LEFT(Arr, LEN(Val)),
        StartCS, EXACT(LeftChar, Val),
        StartNCS, LeftChar = Val,
        SameCS, EXACT(Arr, Val),
        SameNCS, Arr = Val,
        Fuzz, ISNUMBER(IF(Case, FuzzCS, FuzzNCS)),
        Start, IF(Case, StartCS, StartNCS),
        Same, IF(Case, SameCS, SameNCS),
        Result, --(SWITCH(Type, 1, Start, 2, Same, Fuzz)) = 0,
        Return, IF(ERRORS <> 0, ERRORS, Result),
        Return
    )
)

 

[schardt679]

Below is my solution for part 1 (multiple search values across 2D ranges). It is quite long but includes error checking and quite a few variable names to make it easier to understand. The basic idea is converts the search array to a 1-column array and the value array to a 1-row array. This allows MMULT to return an array of trues & falses regarding the search values based on the match type & case sensitivity. Then the resulting array is converted back to the original size. Finally the code checks for errors before returning the results.

= LAMBDA(Array,Search_Value,[Match_Type],[Case_Sensitive],
    LET(
        Arr, Array,
        Val, TOCOL(Search_Value),
        Fnd, Found?,
        Type, IFERROR(--(Match_Type),  3),
        Case, IFERROR(--(Case_Sensitive),  2),
        TypeScan, OR(Type < 0, Type > 2),
        TypeMSG, "Match_Type=0,1,2",
        CaseScan, AND(Case <> {0,1}),
        CaseMSG, "Case_Sensitive=0,1",
        ERRORS, IFS(TypeScan, TypeMSG,  CaseScan, CaseMSG,  1, 0),
        ColCt, CELLCT(Arr, 2),
        RowCt, CELLCT(Val, 1),
        Seq_1, SEQUENCE(RowCt,,, 0),
        ArrCol, TOCOL(Arr),
        ValRow, TOROW(Val),
        FuzzCS, FIND(ValRow, ArrCol),
        FuzzNCS, SEARCH(ValRow, ArrCol),
        LeftChar, LEFT(ArrCol, LEN(ValRow)),
        StartCS, EXACT(LeftChar, ValRow),
        StartNCS, LeftChar = ValRow,
        SameCS, EXACT(ArrCol, ValRow),
        SameNCS, ArrCol = ValRow,
        Fuzz, ISNUMBER(IF(Case, FuzzCS,  FuzzNCS)),
        Start, IF(Case, StartCS,  StartNCS),
        Same, IF(Case, SameCS,  SameNCS),
        TypeSel, SWITCH(Type,  1, Start,  2, Same,  Fuzz),
        TruFal, --(MMULT(--(TypeSel), Seq_1))=0,
        Result, WRAPROWS(TruFal, ColCt),
        Return, IF(ERRORS <> 0, ERRORS,  Result),
        Return
    )
)