How to Find Value In Range in Excel (3 Methods)

Circumstances may demand you to find value from a range. Finding, retrieving are common operations in Excel. Today we will show you how to find value in range. For this session, we are using Excel 2019 (a bit of Excel 365), feel free to use your preferred version.

First things first, let’s get to know about the dataset which is the base of our examples.

Dataset - Excel Find Value In Range

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.

Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

Practice Workbook

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

Find Value in Range

As we mentioned we will find value from a range using the movie dataset, let’s introduce a couple of fields that will hold the searching value and the output.

Fields to store values - Excel Find Value In Range

Here, we have added two fields Find Value and Result distinct to the table.

1. Find Value in Range Using MATCH Function

Hearing “Find Value” a couple of functions, FIND, SEARCH, may come into your mind. But we are afraid these are not a good match for finding within range, what then?

The answer lies in the question. Haha! Yes, we mentioned the word “match”, and that is going to be the function to find the value in a range.

The MATCH function in Excel is used to locate the position of a lookup value in a range. Let’s utilize the function.

Search values - Excel Find Value In Range

Here, we are going to search the value Brad Pitt in the array of actors. So, our formula will be

=MATCH(H4,C4:C14,0)

MATCH Function to find the values

We have set H4 as the lookup_value in MATCH. Then C4:C14 is the range and 0 for the exact match.

This will return the position of the value within the range.

MATCH Function result to find values in a range

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.

If getting the position for the search value is your goal, then you only have to do this.

But if you want to produce a result that allows all to understand whether the value is present or not in the range, then several logical functions, IF and ISNUMBER, are going to help.

The formula will be

=IF(ISNUMBER(MATCH(H4,C4:C14,0)),"Found","Not Found")

MATCH Formula with IF - ISNUMBER to find value in a range

The MATCH function is inside ISNUMBER, which checks whether FIND returns the position or error (when MATCH doesn’t get the character within the string it returns #N/A! error). For a number (position) it returns TRUE

That’s why we have set “Found” as if_true_value for the IF function.

Result of MATCH-IF-ISNUMBER formula

Here, for Brad Pitt MATCH returned a number (we saw earlier). So, the ultimate result is “Found”.

If we search for a value that is not in the range, the formula will return “Not Found”. Result of MATCH-IF-ISNUMBER for value not in range

2. COUNTIF Function to Find Value in Range

We can use the statistical function COUNTIF to find value in 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 IF though.

The formula will be the following one

=IF(COUNTIF(C4:C14,H4)>0,"Found","Not Found")

IF-COUNTIF to find value in a range

Within COUNTIF(C4:C14,H4)>0, C4:C14 is the range and H4 is the value to find.

And as we know COUNTIF counts cells based on criteria, so it will count the cells from the C4:C14 range based on H4. 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.

IF-COUNTIF result of finding value in range

3. Find Value in Range Using VLOOKUP

We can use the VLOOKUP function to find a value in a range. VLOOKUP looks up data in a range organized vertically.

Let’s write the formula using VLOOKUP.

=VLOOKUP(H4,C4:C14,1,0)

VLOOKUP function to find value

H4 is the lookup_value and C4:C14 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.

Result of VLOOKUP - Excel Find Value In Range

We have found the value itself as the result of our formula.

If we search for something that is not in the range the formula will provide #N/A! error.

Value Not Available for VLOOKUP

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

The IFNA function checks whether a supplied value or expression evaluates the Excel #N/A error or not. And replaces result for #N/A!.

The formula will be

=IFNA(VLOOKUP(H4,C4:C14,1,0),"Not Found")

IFNA-VLOOKUP formula to find value in range

We wrapped up the VLOOKUP with IFNA and set “Not Found” as ifna_value. So, when it will not find a value in the range, it will provide “Not Found” as result.

IFNA-VLOOKUP result

But when the value is in the range, the standard VLOOKUP result will be the final output.

IFNA-VLOOKUP formula result

Derive Value from Range Based on Find

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.

The combination of INDEX and MATCH will derive the value. The INDEX function returns the value at a given location in a range.

The formula will be the following one

=INDEX(E4:E14,MATCH(H4,C4:C14,0))

INDEX-MATCH to derive the value from range

We have seen MATCH return the position of the matched value, and then INDEX uses that position value to return the value from the range E4:E14.

We can use the VLOOKUP function to return a value based on the search value. For our example, the formula will be

=VLOOKUP(H4,C4:E14,3,0)

VLOOKUP to derive value from range

Here we have inserted almost the entire table (except the SL. No column) as the range. The column_num_index is 3, which means depending on the match the value will be fetched from the 3rd column of the range. And the third column contains the movie name.

If you are using Excel 365, then another function you can use is XLOOKUP.

The formula using this function will be

=XLOOKUP(H4,C4:C14,E4:E14)

XLOOKUP formula to derive the value

Within XLOOKUP first, we have inserted the search value (H4), then the lookup range (C4:C14), and finally the range (E4:E14) from where we want the output.

XLOOKUP allows you to set the parameter for a value that is not in the range.

=XLOOKUP(H4,C4:C14,E4:E14,"Not Found")

Now if we find a value that is not present in the range, then we will get “Not Found” as the result.

XLOOKUP with N/A handle

Conclusion

That’s all for the session. We have listed approaches 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.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo