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.

select specific data in Excel using the VLOOOKUP function


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.

Sample dataset of students name,marks with id


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.
Pressing the keyboard shortcut

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

  • 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.Enter what to select on the Find what box in the Find and Replace dialog box

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
clicking on the Find in the Find and Replace options in the Home tab

  • 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.

enter what specific text to find and click on Find all text in the file


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,

=VLOOKUP(G4,B5:D14,3,FALSE)
Step 2:
● Finally, press Enter button on your keyboard
Enter VLOOKUP function in G5 to get the marks of the student

● After that, you will get your specific data.
VLOOKUP function extract marks 45 from the range of cell D5:D14


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,
=HLOOKUP(B10,C4:H6,3,FALSE)
  • After that press Enter button on your keyboard.
    Entering the HLOOKUP Function to get the MARKS value in the C10
  • Finally, after doing these steps you will get your desired specific data.

How HLOOKUP function extract marks in C10 by tracking id no

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


Similar Readings


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.

=INDEX(C5:D14, MATCH(C10, C5:C14,0), 2)
● Let’s break down this formula. MATCH(C10, C5:C14,0) will try to look up the value in cell C10 (Name of the student-Williamson) in the range C5 to C14. and will return the relative position of the matched cell. In our case it will give an output value of 6 as the relative position of William in the Student Name column is 6.
Perform the INDEX Function to Select Specific Data in Excel
  • 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.

INDEX function getting the marks by finding the id

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

=MATCH(G4,B5:B14,0)
● Then press ENTER on your keyboard button.
  • MATCH function to get the marks of the students by idHence this is the desired output of our Match function.

How match function get the mark from the range from the id value

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

=IF(D5>=50, "Pass", "Fail")
  • Finally press Enter button on your keyboard.
    application of the IF function to get whether the students get passed or not
  • Then apply the same function to the rest of the cells in the dataset.
  • Fill Handle icon in the corner of the cell E5 Finally we have got the result of the student by applying the IF function.Dragging the Fill handle to the cell E14 to fill the range of cell E5:E14 with result whether students passed or not

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

 

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo