How to look for a value in a list in excel?

In this article, the user will understand the process of how to look for a value in a list in Excel. Excel facilitates several functions and methods to accomplish this task. This article contains two brief examples. The first example guides the process of using the user-defined formula, to evaluate the age of the user. on the other hand, the second example is based on the process of using the kutool to perform the same task more easily without any need to remember any formula, to obtain the same result.

Example 1: To Look for a value in Excel sheet, by using the user-defined formula

Step 1

Consider the below-given table. The provided table contains three columns. The first column contains the names of employees. The second column provided age numbers, and the third column shows the salary of employees.

Step 2

In this example, will understand the use of a user-defined formula, to obtain the required data. To make the result data more detailed, let’s add a cell header above the resultant cell. here, will go to the G1 cell and type “result”. The obtained result will be stored in the G2 cell. type the formula “=INDEX(B2:D5,MATCH("Sam",B2:B5,0),2)”, as depicted in the below-given image −

The explanation for formula −

• INDEX(B2:D5, ...) − The INDEX function is used to retrieve a value from a specified range of cells. In this case, the range B2:D5 is provided as the array from which the value will be returned.

• MATCH("Sam", B2:B5, 0) − The MATCH function is used to find the position of a specified value within a column or row. In this case, "Sam" is the value being searched for within the range B2:B5. The 0 as the last argument indicates an exact match is required.

• 2 − The number 2 specifies the column index from which the value should be retrieved. In the range B2:D5, the value will be returned from the second column, which is column C.

• Putting it all together, the formula finds the position of "Sam" in the range B2:B5 using MATCH, and then uses the INDEX function to retrieve the corresponding value from column C (the second column) in the range B2:D5.

So, the formula will return the value from the cell in column C where "Sam" is found in column B, within the range B2:D5.

Step 3

After typing the above-provided formula. Simply press the “Enter” key. The obtained result value is 45. In this example, the user is trying to calculate the age of employee “Sam”. The final output snapshot is provided below −

Example 2: To Look for a value in excel sheet, by using the user kutools

Step 1

In this example will perform the same task by using the kutools. To do so, go to the “kutools” tab, and then click on the “Formula”. This will further display a few more option tiles. Among them select the second option named “Formula Helper”, and further select the option “Look for a value in the list”. Consider below provided snapshot for a clear and precise understanding of the used options.

Step 2

The above step will open the “Formula Helper” dialog box. In the “Select a formula” section, choose option “Look for a value in list”. Then move to the next section “Argument Input”, there in the table_Array lable, select the complete table range by using the side button, or the user can directly write the provided cell range. In the look_value label, type the element for which the user wants to search the data, and finally at the column label, enter the available search range. Finally, click on the “OK” button.

Step 3

The above step will display the results as 45. As, here also user is evaluating the age for sam. Please note that the values, in the input fields, can be changed by the user according to individual requirements. Consider below provided snapshot for proper reference −

Conclusion

The step-by-step instruction to be depicted in both examples. By following the steps precisely user can easily learn the process of looking up or searching a data value in any large available dataset. The first example uses the user-defined formula whereas the second examples utilize the Kutools to look for a value in Excel.