Mehrab Imtiaz
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])
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")
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)
On its own, the function counts occurrences of a given value within a range.
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")
Read More: Easy Guide to Replace Formula with Value in Google Sheets
Similar Readings
- How to Find Hidden Rows in Google Sheets (2 Simple Ways)
- Find Frequency in Google Sheets (2 Easy Methods)
- How to Find Median in Google Sheets (2 Easy Ways)
- Find Largest Value in Column in Google Sheets (7 Ways)
- How to Find Edit History in Google Sheets (4 Simple Ways)
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.
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 [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.
Related Articles
- How to Find and Delete in Google Sheets (An Easy Guide)
- Use FIND Function in Google Sheets (5 Useful Examples)
- How to Find P-Value in Google Sheets (With Quick Steps)
- Find and Replace with Wildcard in Google Sheets
- How to Use Find and Replace in Column in Google Sheets
- Find All Cells With Value in Google Sheets (An Easy Guide)
- How to Find Correlation Coefficient in Google Sheets
- Find Uncertainty of Slope in Google Sheets (3 Quick Steps)
