Circumstances may demand you to find value from a range. Finding, and retrieving are common operations in Excel. Today we will show you how to find value in a range in Excel with 3 Quick methods. Later, also learn to find and return value in a range as well.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
3 Quick Methods to Find Value in Range in Excel
First things first, let’s get to know about the dataset which is the base of our examples. Here, we have a table that contains several actors from different film industries with one of their popular movies.
Using this dataset we will find a value in a range of values.
1. Use MATCH Function to Find Value in Range
If you want to produce a result that allows all to understand whether the value is present or not in the range, then the combination of the IF, MATCH and ISNUMBER functions is going to help. Let’s see how it works.
- First, add two fields Find Value and Result beside the table.
- Then, insert your required value in Cell G4.
- Now, insert this formula in Cell G5.
- Following this, press Enter.
- As a result, you will see the output as Found because the finding value is preset in the dataset.
- If we search for a value that is not in the range, the formula will return Not Found.
- If getting the position for the search value is your goal, then you can apply this formula.
2. Combine IF & COUNTIF Functions to Search Value in Range
We can use the statistical function COUNTIF to find value in the range. The COUNTIF function counts the number of cells from a range that matches a given condition. The description may raise doubt in your mind that getting the number of cells is not our aim rather find the value in the range. No worries! We will find the value and COUNTIF will play the pivotal role. We also need help from the IF function though.
- To find the value, type this formula in Cell G5.
- Therefore, press Enter and you will get the result.
3. Find Value in Range with VLOOKUP Function in Excel
We can use the VLOOKUP function to find a value in a range. It looks up data in a range organized vertically. To do the task, go through the instructions below.
- First, insert this formula in Cell G5.
- Then, we will get the value itself as the result of our formula.
- If we search for something that is not in the range the formula will provide a #N/A error.
- To get rid of this error and produce a better understandable result for the value which is not in the range, we can use the IFNA function.
- Therefore, put this formula instead.
- On the other hand, when the value is in the range, the standard VLOOKUP result will be the final output.
How to Find & Return Value in Range in Excel
It’s quite common to retrieve a value based on the search value in a range. Let’s say we want to derive the name of the movie by finding the actor’s name in the range. There are several ways to fetch the value. Let’s explore a few most common approaches.
- First, the combination of the INDEX and MATCH functions will derive the value. The INDEX function returns the value at a given location in a range.
- For this, use this formula in Cell G5.
- Second, we can use the VLOOKUP function to return a value based on the search value.
- For our example, the formula will be like this.
- Lastly, if you are using Excel 365, then another function you can use is the XLOOKUP function.
- The formula using the function will be this.
- Along with this, the XLOOKUP function allows you to set the parameter for a value that is not in the range.
- Now, if we find a value that is not present in the range, then we will get “Not Found” as a result of this formula.
That’s all for the session. We have listed 3 quick methods on how to find value in a range in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we might have missed here. Follow ExcelDemy for more resources.