Forum Discussion
Exact text search in a range of cells
Hi, all.
I have a search textbox macro for cell B1, which checks a range of cells in sheet 2, and returns any cells which include the search term.
Sheet 2 has a number of paragraphs in the range of cells.
The search works, but how do I make it an exact search (not case-sensitive), for what is typed in B1.
Eg.
If someone types HR in B1, only cells which include an exact match are returned, and not any words (eg threat, through, synchronise) which include the search term HR.
If someone types Perform in B1, only cells which include an exact match are returned, and not any words (eg performance) which include the search term perform.
This is the formula for the search so far:
=IF((LEN(B1))=0, "Product Search", FILTER('Product List'!A2:F152,ISNUMBER(SEARCH(B1,'Product List'!A2:A152))+ISNUMBER(SEARCH(B1,'Product List'!B2:B152))+ISNUMBER(SEARCH(B1,'Product List'!C2:C152))+ISNUMBER(SEARCH(B1, 'Product List'!D2:D152))+ISNUMBER(SEARCH(B1, 'Product List'!E2:E152))+ISNUMBER(SEARCH(B1, 'Product List'!F2:F152)),"No records found"))
Thanks
=IF(LEN(B1)=0,"Product Search",FILTER(D1:G5,N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(D1:D5," ",",")&", ")))+N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(E1:E5," ",",")&", ")))+N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(F1:F5," ",",")&", ")))+N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(G1:G5," ",",")&",")))))If you want to identify "hr" in a cell with "our hr department" you can try this formula.
Thanks for the response, OliverScheurich.
This seems to work:
https://www.mrexcel.com/board/threads/exact-text-search-in-a-range-of-cells.1227086/#post-6003752
=IF(LEN(B1)=0,"Product Search",FILTER(D1:G5,EXACT(B1,D1:D5)+EXACT(B1,E1:E5)+EXACT(B1,F1:F5)+EXACT(B1,G1:G5)))Does this return your expected result?
