This example will teach you how to create your own search string in Excel.
This is what the table looks like. If you enter a search term in a cell B2, Excel will find matches in the column E and give the result in a column B.
To create this search string, follow the instructions below:
- Highlight a cell D4 and paste the function SEARCH (SEARCH) as shown below by specifying an absolute cell reference V2.
=SEARCH($B$2,E4)=ПОИСК($B$2;E4) - Double click on the autocomplete marker, which is located in the lower right corner of the cell D4to quickly copy the formula to all remaining cells in the column D.
Explanation: Function SEARCH (SEARCH) searches for the starting position of the searched value in the string. Function SEARCH (SEARCH) is not case sensitive. In the word “Tunisia”, the string “uni” has a starting position of 2, and in the word “United States” the starting position is 1. The lower the value, the higher it should be. - Both “United States” and “United Kingdom” return the value 1. How to be? A little later, we will assign unique values to all data using the function RANK (RANK), but for this we need to slightly adjust the result of the formula in the cell D4as shown below:
IFERROR(SEARCH($B$2,E4)+ROW()/100000,"")ЕСЛИОШИБКА(ПОИСК($B$2;E4)+СТРОКА()/100000;"") - Double click on the bottom right corner of the cell again D4to quickly copy the formula to the rest of the cells in the column.Explanation: Function ROW (ROW) returns the row number of the cell. If we divide the line number by a large number and add this value to the result of the function SEARCH (SEARCH), we will always get unique values, and a small increase will not affect the ranking. Now the value for “United States” is 1,00006 and for “United Kingdom” it is 1,00009. In addition, we have added the function IFERROR (IFERROR). If the cell contains an error, such as when the string cannot be found, an empty string (“”) is returned.
- Select cell C4 and paste the function RANK (RANK) as shown below:
=IFERROR(RANK(D4,$D$4:$D$197,1),"")=ЕСЛИОШИБКА(РАНГ(D4;$D$4:$D$197;1);"") - Double click on the right corner of the cell С4to quickly copy the formula to other cells.Explanation: Function RANK (RANK) returns the ordinal value of the value. If the function’s third argument is 1, Excel arranges the numbers in ascending order, from smallest to largest. Since we have added the function ROW (ROW), all values in column D have become unique. As a result, the numbers in the column C are also unique.
- We’re almost done. Function VLOOKUP (VLOOKUP) we will use to extract the found countries (lowest value first, second lowest second, etc.) Select a cell B4 and paste the function VLOOKUP (VLOOKUP) as shown below.
=IFERROR(VLOOKUP(A4,$C$4:$E$197,3,FALSE),"")=ЕСЛИОШИБКА(ВПР(A4;$C$4:$E$197;3;ЛОЖЬ);"") - Double click on the bottom right corner of the cell B4to quickly copy the formula to other cells.
- Change the color of numbers in a column А to white and hide the columns С и D.
Explanation: Function SEARCH (SEARCH) searches for the starting position of the searched value in the string. Function SEARCH (SEARCH) is not case sensitive. In the word “Tunisia”, the string “uni” has a starting position of 2, and in the word “United States” the starting position is 1. The lower the value, the higher it should be.
Explanation: Function ROW (ROW) returns the row number of the cell. If we divide the line number by a large number and add this value to the result of the function SEARCH (SEARCH), we will always get unique values, and a small increase will not affect the ranking. Now the value for “United States” is 1,00006 and for “United Kingdom” it is 1,00009. In addition, we have added the function IFERROR (IFERROR). If the cell contains an error, such as when the string cannot be found, an empty string (“”) is returned.
Explanation: Function RANK (RANK) returns the ordinal value of the value. If the function’s third argument is 1, Excel arranges the numbers in ascending order, from smallest to largest. Since we have added the function ROW (ROW), all values in column D have become unique. As a result, the numbers in the column C are also unique.
Result: Your own Excel search string.
