3 Ways To Check if a Value Exists in a Range in Excel

There are multiple ways to check if a value exists in a range in Excel. The three methods that I tend to use will result in varying results. Therefore, try each and find the one that works best for you. I’ll go over using IF, VLOOKUP, and conditional formatting to check whether a value exists in a cell.

Video Guide: Check a Cell Value in Excel

In my personal experience, here are three best ways to check if a value exists in a range in Excel:

• IF and MATCH
• VLOOKUP
• Conditional Formatting

I will show you how to use each method in detail. Read on to learn more, and don’t forget to access my sample sheet so you can follow along better.

Check if a Value Exists in a Range in Excel Using MATCH

I recommend using the MATCH function nested in IF for Excel to check if a value is in range. I like using this function in my spreadsheets because it’s easy to use and can return its relative position based on the chosen match type, whether it’s a partial or a full match.

If the value isn’t found, the formula can display either #N/A or your chosen text message. Before we look at the formula in action, let me show you the syntax for the MATCH and IF functions.

The syntax for IF is:

=IF(test, trueval, falseval)

The formula requires either two or all three of these parameters to work:

• test: This argument defines the logical test you want to use.
• trueval: This parameter shows the value you want to return if the logical test value returns as TRUE.
• falseval: This optional parameter is the value you want to return if the logical test value returns as FALSE.

In Excel, to check if a value is in a range, we must create a nested formula. The test parameter will contain the MATCH formula, using the following syntax:

=MATCH(value, array, type)

The formula requires at least two parameters to work. These are:

• value: This is a required parameter that you must look for in the array parameter.
• array: The array argument defines the range of cells you want to search.
• type: This is an optional parameter that specifies how Excel should look when looking for the matching value in the array parameter. This parameter can have one of the following three values:
  • 1 – The MATCH function will look for values equal to or less than the value parameter. The values in the array parameter need to be placed in ascending order for this to work correctly.
  • 0 – The MATCH formula will look for the first value that matches the value parameter. The data can be in any order for this to work correctly.
  • -1 – The MATCH function will find the smallest value equal to or greater than the value parameter. The values in the array parameter need to be placed in descending order for this to work correctly.

Now that you understand how the underlying formulas work — let’s take a look at how I use both formulas in your spreadsheet to find the value in a range in Excel:

1. I open the spreadsheet with the data where I want to check if a value exists in a column. Then, I click the cell where I want to write the formula.
2. I will nest the MATCH formula in IF, which means I will enter the IF formula first and then the MATCH formula as one of its parameters.
   • I will first enter the starting part of IF, which is =IF(.
3. I will then add the MATCH formula for the test parameter in IF to check if the range contains the value.
   • The MATCH formula for this example is MATCH(E3,B3:B24,0).
   • The first parameter is the cell with the value I want to look for.
   • The second one includes the cell range, while the third parameter has the value, which tells Excel to look for a perfect match.
4. I added a comma ( , ) to separate the parameters and add the second parameter.
   • This will indicate the text I want to display if a match is found.
   • In this example, I want the function to show “Yes” if a match is found. Therefore, I used Double Quotes ( “ ) within the string values.
5. Finally, add Closing Brackets “ ) ” and press the “Enter” key to execute the function.

Related Reading: 11 Best Excel Courses Online in 2024

Search Excel if a Range of Cells Contains Specific Text Using VLOOKUP

Another method I recommend using when looking for a value in a range in Excel is to utilize the VLOOKUP formula.

This Excel formula is helpful because it can do three things:

1. It can find values in the range.
2. It can search for the initial value in the first column of a cell range.
3. It can return a date point from a cell in the same column.

If the value is found in the column, it is returned as the result. Otherwise, the formula will return an #N/A error.

Before we take a look at an example of the formula in action, below is the syntax for the VLOOKUP function:

=VLOOKUP (value, array, index, range)

The formula requires three parameters to execute, these include:

• value: This argument defines the value you want to look up in the specified cell range in the array parameter.
  • The value you want to look up must be in the leftmost column in the cell range for the array argument.
  • You can also define the parameter as a string or add a reference to the cell.
• array: This parameter specifies the cell range where the function will look for the value parameter.
  • Ensure that the array parameter can be found within the first column of the array for the formula to work correctly.
• index: This argument defines the column number that contains the return value. The leftmost column is always 1.
• range: This is an optional parameter that specifies whether you want VLOOKUP to look for an exact or an approximate match. Use 1 for an approximate match and 0 for an exact match.

Now that you have a better understanding of how the VLOOKUP formula works — let’s take a look  at how I can find an Excel value in a range using the VLOOKUP method:

1. I open the Excel worksheet where I want to find a value in a range formula and click the cell where I want to add the formula.
2. I type in the starting part of the function, which is: =VLOOKUP(.
3. For the first parameter, I will enter the cell address with the cell value.
   • In this case, it is the cell E3.
4. I then added a comma ( , ) to separate the parameters.
5. For the next parameter, I will enter the array address for the table containing the data.
   •  In this case, the second parameter is B3:C24.
6. I added another comma and the third parameter, which tells the function to output the result value from the second column.
7. Then, I type FALSE to indicate that the formula needs to be an exact match.
8. Finally, I add Closing Brackets “ ) ” and press the “Enter” key to execute the function.

Related Reading: Google Sheets vs Excel – Which Is Better In 2024?

Use Excel to Find a Value in a Range Using Conditional Formatting

Conditional Formatting is another great Excel function to check if a value in a cell actually exists. I personally use it to check if a value is found in a column. If the value is found in the column, then special formatting is applied to it in the cell it exists in. I think it’s a great feature to use in spreadsheets, especially when working with student marks, stock inventory, and budgets.

Here is how I would use the Conditional Formatting feature to find if a value is in a range in Excel:

1. I select the cell range where I want to apply the Conditional Formatting.
   • I click and drag my cursor across the cell range to do this.

2. Then, I click on “Home” in the main toolbar. In the “Styles” section, I click on “Conditional Formatting,” which then opens a drop-down menu.
3. I then click the “Highlight Cell Rules” button, which will show a popout menu.
   • Click on the “Equal To” button.

4. This opens a small window in the middle of the screen.
   • Here, I can add the cell address where I want to enter the value.
   • To do this, I click on the “Up Arrow” icon and click the cell to add its reference to the formula.
5. I then select the type of formatting that I want to apply.
   • In my example, I use the default “Light Red Fill with Dark Red Text.”
6. Click on the “OK” button to finalize the settings.

Now, I can enter the value I want to look for using Conditional Formatting. If the value is found in the table, it will be highlighted with a light red fill color.

How To Check if a Partial Value Exists in a Range Using Conditional Formatting

Sometimes, I only have partial values, such as someone’s nickname or a part of an ID number. In this case, I would highlight the data using wildcards in Excel.

For example, assuming we are using the dataset above, I need to find an invoice number that starts with 100. In this case, I would use Conditional Formatting to highlight the cells in the range that start with 100. Here’s how:

1. I select the cells I want to search.
2. I navigate to “Home” > “Conditional formatting” > “Highlight Cells Rules” > “Text that contains”
3. In the “Text That Contains” box that pops up, I type 100* and select the highlighting style I want to use. In the screenshot below, I chose yellow.

And there you have it. Now, you can check a partial value in Excel using Conditional Formatting!

Related Reading: Your Guide to IF CONTAINS Google Sheets Functions

Frequently Asked Questions

How Can I Find a Value in Range in Excel?

You can check for a value in a range in Excel using multiple methods. If you’re looking for a quick way, it’s better to use Conditional Formatting, as it highlights the value in range.

However, if you want to determine the data in another cell, then using the VLOOKUP formula is a better option.

Can I Use a Formula to Find Value in Range?

Multiple formulas allow you to look for a value in a range in Excel. Some of the best ways are using the VLOOKUP formula or the IF and MATCH formula combination. MATCH can be nested in the IF formula’s first argument to find if the matching value exists in the range.

How do I do this in Google Sheets?

With Google Sheets, you’ll use another version of the IF CONTAINS function. Read my guide to see how to find a value of a cell in Google Sheets. Otherwise, check out my guide on the regexmatch function.

Wrapping Up

Above are the three best ways to check if a value exists in a range in Excel. If you feel we missed your favorite alternative method, please let us know in the comments.

Related:

• How To Remove Dashes in Excel (3 Easy Ways)
• How To Combine Date and Time in Excel (2 Methods)
• An Easy 5-Minute Excel “IF Contains Partial Text” Guide

• Ultimate Guide to the Best Excel Keyboard Shortcuts