Find Value in a Range in Google Sheets (3 Easy Ways)

Today, we have a look at how we can find a value in a range in Google Sheets. The idea is simple, and we have a few approaches in mind that we can use depending on our requirements.

Let’s get started.

Table of Contents

3 Ways to Find Value in a Range in Google Sheets

1. Using the MATCH Function

The MATCH function provides us with a near-perfect solution to our problem. The MATCH function syntax:

MATCH(search_key, range, [search_type])

The input fields of our function are pretty self-explanatory. But I’d like to bring your attention to the optional field [search_type]. Since we are looking for exact matches, we will input “0” in this field.

We have created a simple dataset to show our example:

We will search for the name given in the Find field over the range of the Names column. The MATCH function checks the given range and returns the position of the Name in the range.

However, if a match does not exist, the function returns an error.

We can remedy this and give ourselves a conditional value to use to output a meaningful message using the ISERROR function.

ISERROR will return TRUE if there is an error, FALSE otherwise. We can use this output together with the IF function to output a meaningful message.

Our formula:

=IF(ISERROR(MATCH(D3,B3:B12,0)),"Not Found","Found")

2. Find Value Occurrences in Range in Google Sheets

A simple way to find values and their occurrences in Google Sheets is by using the COUNTIF function.

The COUNTIF function syntax:

COUNTIF(range, criterion)

On its own, the function counts occurrences of a given value within a range.

=COUNTIF(B3:B12,D3)

For a deep dive to count occurrences, please see our COUNTIF Contains Text in Google Sheets article.

To Find Only Whether the Value Exists in Range

But we can add a condition (>0) so that the function returns the Boolean TRUE if the value exists in the range, FALSE otherwise. The logic behind it is that if a value is found in the range, it will always be greater than 0.

=COUNTIF(B3:B12,D3)>0

Finally, add a meaningful message with IF. Our formula:

=IF(COUNTIF(B3:B12,D3)>0, "Found", "Not Found")

Similar Readings

3. Lookup Values in Range in Google Sheets

Google Sheets provides us with another function to find values in a range. It is the LOOKUP function.

The LOOKUP function syntax:

LOOKUP(search_key, search_range|search_result_array, [result_range])

The function simply finds and returns the value in range:

=LOOKUP(D3,B3:B12)

But like the MATCH function, we get an error when the searched value does not exist in the range. To that end, we will apply the same treatment of adding a condition, ISERROR, and IF to give a meaningful output.

=IF(ISERROR(LOOKUP(D3,B3:B12)>0),"Not Found", "Found")

However, there are two glaring issues with the base LOOKUP function.

The function does not work properly unless the values in the range are in the ascending order.

Sometimes, even if the value is not present in range, the function hooks onto and returns the nearest or the smallest comparative value to the match.

We can easily overcome this problem by using another iteration of LOOKUP: The VLOOKUP function.

VLOOKUP: The Better Alternative

The VLOOKUP function syntax:

=VLOOKUP(search_key, range, index, [is_sorted])

The [is_sorted] field is where the magic happens. Leaving its value as “0” or FALSE will enable us to find the exact match of the value in Google Sheets.

=VLOOKUP(D3,B3:B12,1,FALSE)>0

Finalizing our formula with IF and ISERROR to give out a meaningful message:

=IF(ISERROR(VLOOKUP(D3,B3:B12,1,FALSE)>0),"Not Found","Found")

Final Words

That concludes all the ways we can use to find value in a range in Google Sheets. We hope that our approaches come in handy for your spreadsheet tasks. For a quick solution, you can always rely on the COUNTIF function. On the other hand, we have VLOOKUP, which can be used to find values from beyond a single column range.

Feel free to leave any queries or advice you might have in the comments section below.