This article explains how to locate cells that contain a certain value within a Microsoft Excel sheet using the "FindVert" function. This Excel function is very useful for finding specific data, such as the salary amount of an employee or the remaining budget calculated on a certain date. The "FindVert" function is included in the versions of Excel for Windows and Mac systems.
Steps
Step 1. Open the Excel document where you want to insert the "Search. Vert" function
Double-click the corresponding file icon.
If you haven't created any documents yet, start Excel, click on the item Blank workbook (for Windows computers only), then enter the data to review using the columns of the sheet.
Step 2. Make sure the data is formatted correctly
The "Search. Vert" function only works if the data is organized in columns (for example in sections arranged vertically). This means that the data must have the headings inserted in the first row of the sheet and not in the leftmost column of the range of values.
If the data to be examined is organized by rows, you will not be able to use the "Find. Vert" function to locate the values you are looking for
Step 3. Understand the structure and syntax of the "Search. Vert" function
The formula of this Excel function consists of four main parameters, each of which refers to specific information on the worksheet:
- Value: this is the cell that contains the value to be searched for within the range of data to be examined. For example, if you want to search for the value stored in the cell F3, you will have to enter this address in the formula and the value to search for will have to be entered in that cell of the Excel sheet.
- Matrix_table- This is the set of cells that make up the data in which to search for the specified value. To indicate the range of data to be analyzed in the formula, you will need to report the address of the cell located in the upper left corner followed by that of the cell located in the lower right corner (without including the column headers). For example, if the data table starts from the cell A2 and reaches the cell A20 extending up to the column F., the value you will need to enter in this formula field will be A2: F20.
-
Index: this is the index of the column of the dataset to be analyzed that contains the value to be returned by the function. The index of the "Lookup. Vert" formula refers to the number of columns in the data table and not to the letters in the Excel sheet. For example, within the latter the columns are indicated by letters TO, B. And C., but within the formula you will have to use numbers respectively
Step 1
Step 2
Step 3.. The index starts from the number 1 which indicates the first column to the left of the data table to be processed, so if your data starts from the F. of the sheet, the corresponding index will be the numer
Step 1..
- Interval: Normally a precise result is requested from the "Find. Vert" function, which can be obtained by entering the logical value FALSE for this parameter of the formula. If you do not need to have an exact match in the formula results, but only an approximate match, then enter the TRUE value for this formula parameter.
Step 4. Select an empty sheet cell
Click on the cell where you want the result returned by the "FindVert" formula to be stored.
Step 5. Begin to compose the syntax of the "Search. Vert" formula
Type the command = Cerca. Vert (to start the composition of the final formula. All the parameters of the "Cerca. Vert" function must be inserted inside the round brackets.
Step 6. Enter the value to search for
Identify the cell containing the value that the "FindVert" function will have to search for within the set of data to be analyzed. Type the address of this cell followed by a comma in the formula "Find. Vert".
- For example, if the value to search for is stored in the cell A12 of the sheet, you will have to report the code A12, inside the formula.
- Separate each formula parameter using a comma. Remember that you cannot use spaces.
Step 7. Enter the set of data to be analyzed
Locate the first cell at the top left of the data range to be processed by the "FindVert" function and enter it in the formula followed by the symbol (:), then locate the cell located in the lower right corner of the data range and type the corresponding address into the formula, this time followed by a comma.
For example, if the data table extends from the cell A2 up to the cell C20, within the formula you will have to report the following A2 code: C20,.
Step 8. Enter the index of the column that contains the values to be returned by the "Lookup. Vert" function
Insert the numeric index of that column into the formula followed by a comma.
For example, if the data table spans columns TO, B. And C. and the result you want is contained in the column C., you will have to report the number 3, within the formula.
Step 9. Enter the code FALSE) as the last parameter of the formula
This will tell the "FindVert" function that you want to have an exact match between the searched value and the result that will be returned. At this point the final formula should look like this:
= Search. Vert (A12, A2: C20, 3, FALSE)
Step 10. Press the Enter key
The formula will be executed immediately and the result will be displayed in the cell where you entered it.
Advice
- Normally when you have to use the "Search. Vert" function within an Excel sheet that contains an inventory you will have to use the name of a list item as the "value" parameter of the formula and the price column as the "index".
- To prevent the values of the "Lookup. Vert" formula cells from being changed automatically when you enter or edit the cells of the dataset to be analyzed, add the "$" symbol before the letter and number that make up the address of each cell present in the formula syntax. For example, the cell A12 within the formula will become $ A $ 12, while the interval A2: C20 will become $ A $ 2: $ C $ 20.
