SOLVED

EXCEL 365 Dynamic Array Search()

%3CLINGO-SUB%20id%3D%22lingo-sub-3414201%22%20slang%3D%22en-US%22%3EEXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3414201%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EWhat%20does%20the%20formula%20calculate%3F%20Cell%20%22%24B%243%20contains%20the%20substring%20%22DE.9%22%20which%20is%20searched%20for%20within%20the%20column%20of%20the%20table%20%22tab_nessie_data%5BProject%5D%22.%20The%20opened%20search%20result%20is%20divided%20by%20the%20found%20results.%20Why%20do%20I%20get%20%22%23WERT!%22%20as%20the%20result%3F%20.%3CBR%20%2F%3E%3CSTRONG%3ENote%3A%3C%2FSTRONG%3E%20Using%20the%20formula%20by%20searching%20for%20numbers%20works%20great.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F374180i21EE307DB14F3A82%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20alt%3D%22Heiko_Tiedemann_0-1653307113878.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks%20for%20your%20support%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3414201%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3414336%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3414336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398372%22%20target%3D%22_blank%22%3E%40Heiko_Tiedemann%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESince%20you%20divide%20text%20on%20number%2C%20like%20%3CSTRONG%3E%3D%22text%22%2F1%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3444275%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3444275%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank's%20for%20the%20answer%20to%20my%20question.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EIs%20there%20a%20solution%20to%20my%20challenge%20from%20your%20point%20of%20view.%26nbsp%3BImportantly%2C%20the%20solution%20works%20in%20conjunction%20with%20the%20Filter()-%2FArray%20function.%20The%20data%20area%20is%20extremely%20extensive%2C%20so%20that%20only%20a%20solution%20with%20a%20very%20low%20run-time%20is%20practicable.%3C%2FP%3E%3CP%3EI'm%20looking%20forward%20to%20your%20proposal.%3CBR%20%2F%3EMany%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3444339%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3444339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398372%22%20target%3D%22_blank%22%3E%40Heiko_Tiedemann%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20you%20can%20start%20by%20explaining%20what%20the%20challenge%20is.%20As%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bexplained%2C%20all%20your%20formula%20does%20is%20divide%20an%20array%20of%20texts%20by%20an%20array%20of%20numbers%201.%20Hence%2C%20it%20generates%20only%20VALUE%20errors.%20Obviously%2C%20that's%20not%20your%20goal.%20But%20what%20do%20you%20want%20to%20achieve%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3444524%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3444524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BAlphanumeric%20characters%20are%20stored%20in%20a%20column%20with%20over%20one%20hundred%20thousand%20cells.%26nbsp%3BWhen%20entering%20characters%20in%20a%20form%20field%2C%20the%20content%20in%20the%20dropdown%20menu%20should%20be%20restricted%20with%20each%20additional%20character%20(without%20double%20entries).%3CBR%20%2F%3EWhen%20using%20numbers%2C%20my%20solution%20works%20very%20quickly%20and%20precisely.%3C%2FP%3E%3CP%3E%3DSORTIEREN(EINDEUTIG(WENN(ODER(cap_id%3D%22%20%22%3Bcap_id%3D%22%22)%3B%22%22%3BFILTER(tab_data%5BID%5D%3B%3CBR%20%2F%3EWENNNV(tab_data%5BID%5D%3DWENNFEHLER(tab_data%5BID%5D%2F(SUCHEN(cap_id%3Btab_data%5BID%5D)%26gt%3B%3D1)%3B%22%22)%3B%22%22)))%3BFALSCH%3BFALSCH)%3B1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F376098i16F8C9B8B091E3EF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20alt%3D%22Heiko_Tiedemann_0-1653902502801.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3444616%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3444616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398372%22%20target%3D%22_blank%22%3E%40Heiko_Tiedemann%3C%2FA%3E%26nbsp%3BSorry.%20Difficult%20to%20follow%20based%20on%20a%20picture%20and%20a%20German%20formula.%20But%20I%20guess%20that%20when%20you%20work%20with%20numbers%2C%20dividing%20that%20number%20by%201%20results%20in%20that%20number.%20but%20perhaps%20the%20attached%20file%20is%20something%20you%20had%20in%20mind.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3445027%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3445027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%20Riny%2C%3CBR%20%2F%3EThis%20is%20a%20simple%20and%20great%20solution.%20(Please%20do%20not%20get%20this%20wrong)%3CBR%20%2F%3EI%20made%20some%20changes%20so%20that%20the%20function%20works%20on%20the%20filters%20()%20regardless%20of%20the%20length%20and%20position.%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DFILTER(Table1%5BProject%5D%3BIFERROR(SEARCH(%24B%244%3BTable1%5BProject%5D%3B1)%26gt%3B%3D1%3B%22FALSE%22))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20question%3A%20Is%20there%20a%20solution%20to%20suppress%20%22%23CALC%22%20(%22CALC%22)%20as%20an%20erroneous%20result%2C%20such%20as%20%22IFERROR%22.%3CBR%20%2F%3ETHX%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3445094%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3445094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398372%22%20target%3D%22_blank%22%3E%40Heiko_Tiedemann%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20the%20third%20optional%20argument%20in%20FILTER%20(i.e.%20%3CEM%3Eif_empty%3C%2FEM%3E).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(FILTER(Table1%5BProject%5D%2CSEARCH(%24B%244%2CTable1%5BProject%5D%2C1)%26gt%3B%3D1)%3CSTRONG%3E%3CFONT%20color%3D%22%23DF0000%22%3E%2C%22%22%3C%2FFONT%3E%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20need%20for%20the%20IFERROR%20on%20the%20SEARCH%20part%20either.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3445159%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3445159%22%20slang%3D%22en-US%22%3EHi%20Riny%2C%3CBR%20%2F%3EI'm%20verry%20happy%20with%20your%20Support.%20Great%20Job%20%3Athumbs_up%3A%20See%20you%20soon%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3445158%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20365%20Dynamic%20Array%20Search()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3445158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398372%22%20target%3D%22_blank%22%3E%40Heiko_Tiedemann%3C%2FA%3E%26nbsp%3BOoops!%20Sorry%2C%20but%20I%20missed%20the%20purpose%20of%20the%20IFERROR%20in%20your%20original%20formula.%20I%20believe%20you%20need%20to%20have%20it%20as%20you%20had%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20would%20be%20like%20this%3A%3C%2FP%3E%3CP%3E%3DIFERROR(FILTER(Table1%5BProject%5D%2CIFERROR(SEARCH(%24B%244%2CTable1%5BProject%5D)%26gt%3B%3D1%2C0))%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello everyone,

What does the formula calculate? Cell "$B$3 contains the substring "DE.9" which is searched for within the column of the table "tab_nessie_data[Project]". The opened search result is divided by the found results. Why do I get "#WERT!" as the result? .
Note: Using the formula by searching for numbers works great.

 

Heiko_Tiedemann_0-1653307113878.png

Thanks for your support

9 Replies

@Heiko_Tiedemann 

Since you divide text on number, like ="text"/1

@Sergei Baklan Thank's for the answer to my question. 

Is there a solution to my challenge from your point of view. Importantly, the solution works in conjunction with the Filter()-/Array function. The data area is extremely extensive, so that only a solution with a very low run-time is practicable.

I'm looking forward to your proposal.
Many thanks

@Heiko_Tiedemann 

Perhaps you can start by explaining what the challenge is. As @Sergei Baklan explained, all your formula does is divide an array of texts by an array of numbers 1. Hence, it generates only VALUE errors. Obviously, that's not your goal. But what do you want to achieve?

@Riny_van_Eekelen Alphanumeric characters are stored in a column with over one hundred thousand cells. When entering characters in a form field, the content in the dropdown menu should be restricted with each additional character (without double entries).
When using numbers, my solution works very quickly and precisely.

=SORTIEREN(EINDEUTIG(WENN(ODER(cap_id=" ";cap_id="");"";FILTER(tab_data[ID];
WENNNV(tab_data[ID]=WENNFEHLER(tab_data[ID]/(SUCHEN(cap_id;tab_data[ID])>=1);"");"")));FALSCH;FALSCH);1)

Heiko_Tiedemann_0-1653902502801.png

 

@Heiko_Tiedemann Sorry. Difficult to follow based on a picture and a German formula. But I guess that when you work with numbers, dividing that number by 1 results in that number. but perhaps the attached file is something you had in mind.

 

@Riny_van_Eekelen 
Hi Riny,
This is a simple and great solution. (Please do not get this wrong)
I made some changes so that the function works on the filters () regardless of the length and position. 

=FILTER(Table1[Project];IFERROR(SEARCH($B$4;Table1[Project];1)>=1;"FALSE"))

 

Another question: Is there a solution to suppress "#CALC" ("CALC") as an erroneous result, such as "IFERROR".
THX

best response confirmed by Heiko_Tiedemann (Occasional Contributor)
Solution

@Heiko_Tiedemann 

Use the third optional argument in FILTER (i.e. if_empty). 

 

=IFERROR(FILTER(Table1[Project],SEARCH($B$4,Table1[Project],1)>=1),"")

 

No need for the IFERROR on the SEARCH part either.

 

 

 

 

@Heiko_Tiedemann Ooops! Sorry, but I missed the purpose of the IFERROR in your original formula. I believe you need to have it as you had it.

 

So it would be like this:

=IFERROR(FILTER(Table1[Project],IFERROR(SEARCH($B$4,Table1[Project])>=1,0)),"")

Hi Riny,
I'm verry happy with your Support. Great Job See you soon