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.

## How to Find Value in Range in Excel: 3 Quick Methods

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

`=IF(ISNUMBER(MATCH(G4,C5:C12,0)),"Found","Not Found")`

- Following this, press
**Enter**. - As a result, you will see the output as
**Found**because the finding value is preset in the dataset.

**the MATCH function**checks whether the value is present in the

**Cell range C5:C12**. When it doesn’t get the character within the string it returns

**#N/A**error. Alongside this,

**the ISNUMBER function**checks if the value in

**Cell G4**is numeric or not. Lastly,

**the IF function**makes a comparative statement among them.

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

`=MATCH(G4,C5:C12,0)`

**Cell G4**as the

**lookup_value**in the

**MATCH**function. Then

**C5:C12**is the range and

**0**for the exact match. As a result, you can see

**Brad Pitt**is the

**2nd**in our table, and the formula returned that number. So, we have found the value in the range.

### 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 **the COUNTIF function** will play the pivotal role. We also need help from **the IF function** though.

- To find the value, type this formula in
**Cell G5**.

`=IF(COUNTIF(C5:C12,G4)>0,"Found","Not Found")`

- Therefore, press
**Enter**and you will get the result.

**COUNTIF(C5:C12,G4)>0**,

**C5:C12**is the range and

**G4**is the value to find. As we know

**the COUNTIF function**counts cells based on criteria, so it will count the cells from the

**C5:C12**range based on

**G4**. If it finds the value, the result will be greater than

**0**. If the value is greater than

**0**, it means the value is found in the range. And the

**if_true_value**(

**Found**) will be the answer.

### 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**.

`=VLOOKUP(G4,C5:C12,1,0)`

- Then, we will get the value itself as the result of our formula.

**G4**is the

**lookup_value**and

**C5:C12**is the range,

**1**is the

**column_num**, and

**0**is for an

**exact match**. This will neither deliver the position nor a

**Boolean value**, rather it will retrieve the value corresponding to the findings.

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

`=IFNA(VLOOKUP(G4,C5:C12,1,0),"Not Found")`

**The IFNA function**checks whether the supplied value in

**Cell G4**evaluates the Excel

**#N/A**error or not. Therefore it replaces the result for

**#N/A**. We wrapped up the

**VLOOKUP**with

**IFNA**and set “

**Not Found**” as

**ifna_value**. So, when it does not find a value in the range, it will provide “

**Not Found**” as a result.

- On the other hand, when the value is in the range, the standard
**VLOOKUP function**result will be the final output.

**Read More:**

- How to Find Last Row with a Specific Value in Excel
- Excel Find Last Column With Data
- How to Find Highest Value in Excel Column

## 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**.

`=INDEX(D5:D12,MATCH(G4,C5:C12,0))`

**MATCH**return the position of the matched value, and then

**INDEX**uses that position value to return the value from the

**Cell range D5:D12**.

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

`=VLOOKUP(G4,C5:D12,2,0)`

**SL. No**column) as the range. The

**column_num_index**is

**2**, which means depending on the match the value will be fetched from the

**2nd column**of the range. And the second column contains the movie’s name.

- Lastly, if you are using the
**Microsoft 365**version, then another function you can use is**the XLOOKUP function**. - The formula using the function will be this.

`=XLOOKUP(G4,C5:C12,D5:D12)`

**the XLOOKUP function**, we have inserted the search value (

**G4**), then the lookup range (

**C5:C12**), and finally the range (

**D5:D12**) from where we want the output.

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

`=XLOOKUP(G4,C5:C12,D5:D12,"Not Found")`

**Read More:**

- How to Find Lowest Value in an Excel Column
- How to Use Excel Formula to Find Last Row Number with Data
- How to Find Multiple Values in Excel

**Download Practice Workbook**

You are welcome to download the practice workbook from the link below.

## Conclusion

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.

## Excel Find Value in Range: Knowledge Hub

- How to Find First Value Greater Than in Excel
- How to Find First Occurrence of a Value in a Range in Excel
- How to Find Value in Column in Excel
- How to Find First Occurrence of a Value in a Column in Excel
- How to Check If a Value is in List in Excel
- Find Last Value in a Range in Excel
- [Solved!] CTRL+F Not Working in Excel

- Lookup Value in Column and Return Value of Another Column in Excel
- How to Find Top 5 Values and Names in Excel
- Find Text in Excel Range and Return Cell Reference
- How to Search Text in Multiple Excel Files
- How to Get Top 10 Values Based on Criteria in Excel
- How to Create Top 10 List with Duplicates in Excel

**<< Go Back to Excel Range | Learn Excel**

This is an excellent and very helpful article; thanks! But what I need is not H4 but to be able to loop through one list to see if its values are in another list. For example, my “Actor” column would be one long email list “All” and my “Movie” column would be a shorter list “Some”, and I need to see if the values in the “All” list are in the “Some” list.