Exact text search in a range of cells

[Reyaaz]

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) 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'!D2152))+ISNUMBER(SEARCH(B1, 'Product List'!E2:E152))+ISNUMBER(SEARCH(B1, 'Product List'!F2:F152)),"No records found"))

Thanks

[Pecoflyer]

Please post a sample sheet ( no pics please) with 10-20 lines of data and some expected results.
Thx

[Reyaaz]

Thanks for your response, Pecoflyer.

In the attached example, the user types HR into the search box and a number of rows are pulled up which include HR.

A17 is fine as a search result, as it includes HR as a single word.

C15 and B16 are not, as they only include the word through, and not HR.

The info is gathered from the second sheet.

[Reyaaz]

This seems to work:

Exact text search in a range of cells | MrExcel Message Board

[ArviLaanemets]

Originally Posted by Reyaaz

In the attached example, the user types HR into the search box and a number of rows are pulled up which include HR.

A17 is fine as a search result, as it includes HR as a single word.

C15 and B16 are not, as they only include the word through, and not HR.

Instead "HR" search for "HR " (as leftmost characters in cell), " HR ", or " HR" (as rightmost characters in cell)

Edit: I.e. instead of single check, you have to check all 3 possibilities - in case any of them does pass, the record is displayed!

[Pecoflyer]

Originally Posted by Reyaaz

Thanks for your response, Pecoflyer.

In the attached example, the user types HR into the search box and a number of rows are pulled up which include HR.

A17 is fine as a search result, as it includes HR as a single word.

C15 and B16 are not, as they only include the word through, and not HR.

The info is gathered from the second sheet.

As I said, please post a sample sheet NO PICS !

OTOH your problem seems to be solved somewhere else . Please read A message to forum cross posters - Excelguru