How to Select Specific Data in Excel (6 Easy Methods)

Undoubtedly Microsoft Excel helps us to do our work easily. Seeking a large Excel Spreadsheet is not always easy. In this article, I’ll show you 6 methods by which you can easily select specific data in excel based on their values on different conditions.

Below we presented a process where we used the VLOOKUP function to determine and extract the marks of the students using the VLOOKUP Functions.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

6 Easy Methods to Select Specific Data in Excel

Suppose, we have a datasheet where ID, Marks, and Student Names are given in Column B, column D, Column C respectively. We will have to select some specific data using these values. In this section, we will discuss six easy methods to do it.

1. Using the Keyboard Shortcuts

Using the keyboard shortcuts is the easiest way to find the specific data in Microsoft Excel. To quickly select specific data in Excel, execute the following steps.
Steps:
● At first on the keyboard press Ctrl + F button.

● After that, the Find & Replace dialog box will appear.

• Finally in the Find what text box insert the specific data that you want and click on Find All box. . Then you will see the cells which have a character named Anny.

2. Use of the Find Command to Select Specific Data in Excel

Suppose,  in our dataset we want to find out the specific student name. Let’s follow the following steps to find out the name of that student.
Steps:
● At first go to Home > Editing > Find & Replace > Find

• After that, the Find & Replace dialog box will appear.
• Finally in the Find what text boxenter the name of that student you want to find out and click on the Find All box. Now, you will get your desired output in the spreadsheet cell.

3. Apply the Lookup Functions to Select Specific Data in Excel

In MS-Excel you can easily find specific data by using the LOOKUP function. Looking through a single column or row to find a particular value from the same place in a second column or row is the application of the LOOKUP Function. There are, however, two types of LOOKUP Function and they are-
● The VLOOKUP Function
● The HLOOKUP Function
We will learn both of the functions in this section.

3.1. Insert the VLOOKUP Function

VLOOKUP stands for Vertical Lookup. Now, we will find the marks of Anny by using the VLOOKUP Function.

Step:

• Here we want to find the obtained marks of the ID 1309.
• Then For that you should follow the ways:In cell G5 we will apply our function. To do that, type in G5, =VLOOKUP()
• Our lookup values are in cell G4. So we will apply this followed by a comma
• Moreover our table range is B5:D14
• Then enter the column_index_number 3
• As we want the exact match, our range_Lookup_value is False.

Following these steps,  the formula becomes,

● After that, you will get your specific data.

3.2. Use the HLOOKUP Function

HLOOKUP stands for Horizontal Lookup. Where VLOOKUP finds corresponding values in another column, HLOOKUP finds corresponding values in a particular row. Now, we will find specific data by using the HLOOKUP Function.
To find specific data while performing the HLOOKUP Function you obey the following instructions,

Steps:

• At First, select a cell. here our selected cell is C10, type =HLOOKUP().
• Then we will enter the lookup_value in the parentheses, enter Cell B10, followed by a comma.
• Our table_range is C4:H6
• Now, inject row_index_number. Our value is 3
• We want the exact match for our values. In that case, enter the range_Lookup_value as FALSE
• The HLOOKUP Function equals to,

• After that press Enter button on your keyboard.
  
• Finally, after doing these steps you will get your desired specific data.

Read More: How to Use INDEX and Match for Partial Match (2 Ways)

Similar Readings

• Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
• INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)
• [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)
• Examples with INDEX-MATCH Formula in Excel (8 Approaches)
• INDEX MATCH vs VLOOKUP Function (9 Examples)

4. Perform the INDEX Function to Select Specific Data in Excel

Using VLOOKUP has some limitations. The VLOOKUP function can only look up a value from left to right. To avoid this, use the combination of INDEX and MATCH functions instead.
Let’s say, we want to find out the marks of a specific student named Williamson. To find out his marks using INDEX and MATCH function–
Steps:
● At first, select the cell you want to have the return value in. In our case, we have selected G5
● Then write the below formula in that cell or the formula bar.

• After that the INDEX function takes three arguments precisely. The first one C5:D14 is the cell range it is going to work on.
• The second one MATCH(C10, C5:C14,0) will provide the relative cell position of the student which marks we are going to find. In this case, we are looking for the marks of Williamson, so it will give us the value of 6.
• And the third argument will define the relative position of the column it is going to look up against the relative cell value in the second argument. The relative position of the Marks column is 2. Hence, the value is 2.
• Finally after pressing ENTER on the keyboard, we will get an output value of 64 which is the marks of Williamson.

Read More: Excel INDEX MATCH to Return Multiple Values in One Cell

5. Apply the MATCH Function

In Excel, the MATCH function investigates a value in an array and returns the relative position of that criteria.

To find a matching value follow the steps:
Steps:
● At first, select a cell (i.e, G6 ), write =MATCH().
●Enter lookup_value like Cell G5 in the parentheses, followed by a comma
●Then inject the lookup_range, we have B5:B14.
●Now, enter the exact match type 0
●So Our formula becomes

• Hence this is the desired output of our Match function.

Read More: Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)

6. Using the IF Function 

While working with excel you can also select specific data by using IF FUNCTION. It is a logical function. In our dataset, we can easily find out if a student passed or failed in his/ her examination by using the IF function.

Steps:
Applying the IF Function you should follow steps:

• First, select a cell (i.e, E5), type =IF()
• Then type logic in the parentheses like D5>=50
• After that If D5 is satisfied then he/she will pass. Otherwise, he/she will fail

The function is

• Finally press Enter button on your keyboard.
  
• Then apply the same function to the rest of the cells in the dataset.
• Finally we have got the result of the student by applying the IF function.

Read More: IF with INDEX-MATCH in Excel (3 Suitable Approaches)

Conclusion

In this article, we use multiple Excel features to select specific data in Excel. Hope this article helps to solve your problem. Please do comment below if you have further queries or have something to add.

Related Articles

• INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)
• INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)
• Index Match Multiple Criteria in Rows and Columns in Excel
• How to Match Multiple Criteria from Different Arrays in Excel
• Index Match with Multiple Matches in Excel (5 Methods)
• Excel Index Match single/multiple criteria with single/multiple results