The well-known Microsoft Office Excel tool is a must-have in virtually any office around the world. This peculiar tool allows a multitude of functions, so knowing how to make a Search Engine in Excel is one of those things that you should know to facilitate the use of the application.
In any case, the process that we will teach you today should not be confused with looking for words in a range or range of cells and highlighting them in Excel , on the other hand, the one indicated is also a perfectly possible process.
How to Make a Finder in Excel ? – Excel Data Finder ?
As we indicated before, with Excel the functions of the work are facilitated, with this useful tool we can organize huge amounts of information , make complicated calculations in an instant and observe the development of our work or company.
In this tutorial we will teach how to make an internal search engine that will further squeeze all the exceptional functions that we have in Excel , an action that added with looking for values and finding a number in a column , can be quite useful to maintain order in your tables.
How to make a search engine in Excel
To make a search engine in Excel it is necessary to have an organized table first. These types of search engines are used precisely to search for specific data in databases of considerable length.
To make a search engine in a table follow these steps:
There are many ways to make a search engine in Excel , however, the following method is applicable in practically any type of table. Today we will focus on the most basic data tables, but it is also possible to search for data in two or more Excel sheets .
Make a Finder in Excel (Part One)
- This time we will focus on a table of basic user data. Our purpose will be to enter the User’s Code (which for other purposes could well be the DNI, Identification Card or Identification Card number) so that the user’s information is shown, which in this case will be the Name and Date of birth .
- The first step is to create a header similar to the one that integrates the data, in our case it would be to make one that includes the Code, Name and Date of Birth columns, in another place in the table. For practical purposes we will choose from column F, but the place does not matter, you can choose the one that seems best to you.
- The next step is to open the Formulas option. In this tab you should open Search and Reference .
Make a Finder in Excel (Part Two)
- In Search and reference is the formula that we need to ” Search” click on it. A new tab will appear that presents us with two options, select the desired value: Vector .
- Here are three factors that you must define: Lookup_Value, Compare_Vector, and Result_Vector.
- Searched value is the search object, in this case it is the code. Click on the blank for Search_Value and then on the table where you will write to search. In our case it would be just below the extra header we have created, specifically under the Code section .
- Comparison value, as the name suggests, is the value that will be compared with the search one. In this case, we are interested in user codes. For this reason, here you must select all the existing user codes. Drag from the first to the last.
- Result value is what will be displayed after entering and comparing a code. In this case we will require the name. Therefore, select the entire name column and click OK .
- After this, when searching for a value, in this case a user code, the name of said user will be reflected in our new header.
You can also apply the search engine to other columns
- To show us information from other columns, it is as simple as copying the formula and rethinking it. In this case you must copy the Name formula, which is the one we will use in the Date of Birth column .
- We will copy this formula, which in our case should be something like “SEARCH (F3; TABLE2 [CODE]; 3TABLE2 [NAME])”, then proceed to paste it in the Birth Date of the new header.
- After that, change the name to “= SEARCH (F3; TABLE2 [CODE]; TABLE2 [DATE OF BIRTH])”.
- By doing this process, when writing the code of a user, the Name and Date of Birth information will appear. For larger tables the procedure is the same, you can change the formula, but always modifying the second value, that is, the result.
With the previous tutorial on how to make a Finder in Excel , you can easily access data from large tables. It is especially useful for databases of considerable length, such as payroll or student notes. Apply this useful Excel tool right now!
In turn, using the VLOOKUP or HLOOKUP function in an Excel spreadsheet is usually very useful to extend its capabilities when using this popular application, seeing that it is relatively similar to the function that we discussed in the previous guide.
