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.

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])

match function syntax to find value in range in google sheets

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:

dataset to fin value in range in google sheets

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.

returning the position of the existing value using the match function

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

we get an error if the checked value does not exist

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")

using match function to find value in range in google sheets

Read More: How to Search in Google Spreadsheet (5 Easy Ways)

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)

countif function syntax

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


using countif to find occurrences of value in a range in google sheets

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.


adding a condition to the countif

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

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

using countif to find value in range in google sheets

Read More: Easy Guide to Replace Formula with Value in Google Sheets

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 lookup function syntax

The function simply finds and returns the value in range:


lookup function finds and returns the given value

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")

using lookup to find value in range in google sheets

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

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

lookup function gives wrong output if the values aren't in the ascending order

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

lookup sometimes takes the nearest value instead of the correct match

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

Read More: How to Find the Range in Google Sheets (with Quick Steps)

VLOOKUP: The Better Alternative

The VLOOKUP function syntax:

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

the vlookup function syntax

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.


adding a condition to the vlookup function

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")

using vlookup to find value in range in google sheets

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.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply