SOLVED

Search part text in different columns and add 1 to return value

New Contributor

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.

Schermafbeelding 2021-12-16 om 10.24.10.png

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

Schermafbeelding 2021-12-16 om 10.25.55.png

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. 

2 Replies
best response confirmed by YuanTSC (New Contributor)
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.