Dec 16 2021 01:42 AM
Hi I'm trying to make a search options for finding id codes [columnA] of material [columnB] and type [columnC] options. The value return can be from two different columns. But also the entered search word i don't want it to be exact.
The result is a list of ID codes of all materials or type containing parts of the search word.
With a combination of this synthaxis
=X.VERGELIJKEN($C$2;(VERSCHUIVING(Materialen!B:C;E1;0;1000;1));2)+E1
and
=ALS(ISFOUT(INDEX(Materialen!A:A;$E2));"";INDEX(Materialen!A:A;$E2))
It returns only value looked up in column B. Even if part of the tekst is found in column C.
and add 1
I used
(X.ZOEKEN(C2;Materialen!B:B;Materialen!A:A;(X.ZOEKEN(C2;Materialen!C:C;Materialen!A:A;"Probeer iets anders";2;1));2;1))
to have it searched in both columns. That works. But only if its exact that word.
And still I don't know how to implement this in the above formula so the result is a list of ID codes.
Thank you.
Dec 16 2021 02:15 AM - edited Dec 16 2021 02:16 AM
Solution@YuanTSC Since your Excel version supports dynamic array functions, use FILTER as the main function to achieve what you want. Example workbook attached. The formula I used should translate into Dutch automatically when you open it. Hopefully it helps you find a solution for your real schedule.
Dec 19 2021 11:57 PM
@Riny_van_Eekelen Thank you. Ill try that.
Dec 16 2021 02:15 AM - edited Dec 16 2021 02:16 AM
Solution@YuanTSC Since your Excel version supports dynamic array functions, use FILTER as the main function to achieve what you want. Example workbook attached. The formula I used should translate into Dutch automatically when you open it. Hopefully it helps you find a solution for your real schedule.