The search function in Excel allows you to search for a text string occurrence within another text string.
Use to identify all rows where, for example, a description field, contains a certain word. Perhaps you are looking for every row which contains the word "VAT".
where column A contains the description field.
|1||Novation of lease||3||FALSE||Matching String|
|2||Some other text||#VALUE||TRUE|
If the string is not found the #VALUE error is returned. If the string is found you are given the starting position it occurs. In the above example, the word "VAT" starts at the third character.
To tidy up the results you can use the ISERR function to determine whether or not the error will occur. i.e. FALSE means the text is found, TRUE means it isn't.
Then use the IF function for a final tidy up. If the ISERR function is TRUE don't return anything, otherwise display "Matching String" in the cell.
Use the SEARCH function with caution. In this example I was searching for the word VAT. This letter sequence is contained in other words, such as Novation, which may not be quite what you were you looking for in your results. If you are running the function over a large dataset you might not pick up this logical error.
Posted in: Business
Tags: SEARCH | ISERR | IF | Excel | Logic Error
© Eriginal Ltd 2011, all rights reserved