We often need to check our data in excel is a value in a list to find something quickly. There are more than a few ways of doing this task. These tips and tricks that we will be showing you will help find a string of text or amount that you can search for in your data sheets quite easily. You can look for a number or text in minutes in the blink of an eye. So, lets us begin our lesson today.

So today, we will be working with sales data for a fruit company. Here the data is divided into area, products and sales. Then, we will use our ways to extract what we are looking for from the data.

**Excel is a value in a list. Using The Find Function**

The first tool we will be using is the find tool from MS excel. You will just need to enter the product name or sales figure, and the Find tool will find what you are looking for and show it to you. This is the easiest to look for a value in a list. These tools can save a lot of time for you.

**Step 1:** Open up an MS excel worksheet and enter the data; then head to the upper right side of the home tab, click the search icon, and select find.

**Step 2:** A popup box will open and enter your search query, and press Find Next to find What you are looking for. This can look for multiple instances of the same product too.

**Isnumber And Match For Excel Is a Value In A list**

Here, we have certain things in the Item column which we want to verify in the list of the goods in the product column. After confirming, the result will display in the Result column. In this way, we are completing this work by utilizing the ISNUMBER function and the MATCH function. We will require a formula for this task. The Formula is “=ISNUMBER(MATCH(E2,$B$2:$B$6,0))” Here, E2 Is the search query, and b2 to b6 is the search range. If the search query is in the search range, then it will return A true if the Query is missing in the Search range, then it will return false.

**Step 1:** Open the spreadsheet and enter the search query and apply the Formula in an adjoining cell, and press enters to apply the Formula. You can adjust the Formula to your requirements.

**Step 2:** You Can use multiple queries by just drag the autofill handle downwards and it will extend the formula for you.

**Using Countif For excel is a value in a list**

We can also use countif to do our task easily with a simple formula. Countif will also return True Or False results For Search queries. When COUNTIF locates the value in the list, it will return a number dependent on this value’s presence. Since this number will be larger than zero, the output will be true, but if the value is not present in the list, the output will be false. The Formula We will be using is “=COUNTIF($B$2:$B$6,E2)>0” E2 is the cell for the Query, and B2 to B6 is the search range.

**Step 1:** Open up the previously used workbook, remove the old Formula, and apply the new Formula.

**Step 2:** Press enter to see the results and you can drag the autofill handle downwards to apply the formula to more queries.

**Combining IF And Countif To excel is a value in a list **

We can combine the If and Coutif to get Matched and Not Matched data to look for a value in a list. The Formula will look for the search query in the list, and if the search query is in the list, the Formula will return a matched result. If not found, then it will return a not matched result. The Formula that we will be using is “=IF(COUNTIF($B$2:$B$6,E2)>0,”Matched”,”Not Matched”)” Here, E2 Is the Search Query, and B2 to b6 is the search range.

**Step 1:** Open up the excel spreadsheet, remove the old Formula, and enter the new Formula. You can modify the search query and the search range. You can even change the value in the search cell.

**Step 2:** Results will be shown in matched and not matched.

**Wildcard Partial match For Excel Is A Value In A-List**

We can also use wildcard and partial match for finding a value in a list. We have Mango and Pear in the Item column in the following table, but they are not entirely matched in the Product list. Because of this, we have changed Apple in the dataset to Green Apple to discuss this scenario. We are using the IF function and the COUNTIF function to verify the values that are partly matched in the list that we have here. Same as before, this function also returns Matched and Not Matched Results. The formula we will use is

“=IF(COUNTIF($B$2:$B$6,”*”&E2&”*”)>0,”Matched”,”Not Matched”)”

Formula Description:

$B$2:$B The range in which you verify your desired value is $6, and the value you are seeking is E5. The “*” operator links this value with the value you are looking for. The symbol “*” is used if the values only partly match.

When COUNTIF detects the value in the list, it will return a number based on the occurrence of this value. Since the number will be larger than zero, IF will return Matched unless the value is not present in the list, in which case it will return Not Matched.

**Step 1:** Open the spreadsheet, remove the previously used Formula, and enter the new Formula. You can modify the search queries or the search range.

**Step 2:** You can now see the matched data; drag the autofill handle downwards to apply the Formula to more cells.

**Note:** You can Change the Preconfigured Output by changing the words inside the “; you can do this for steps 4 and 5.

**Iserror And Vlookup For Excel Is A Value In A List**

We can also combine iserror and vlookup to complete our task; this Formula is complex but delivers the results perfectly. The Formula that we will be using is “=IF(ISERROR(VLOOKUP(E5,$B$5:$B$10,1,FALSE)),”Not Matched”,”Matched”)” The Formula is a bit complex. Just enter the data correctly, or it might not work.

Formula Description

VLOOKUP will search for the value of the cell E2 in the range denoted by $B$2:$B$6, where 1 represents the column index number, and FALSE indicates an exact match is desired.

The ISERROR function will return TRUE if the value in question cannot be determined or does not match; otherwise, it will return FALSE. TRUE will be interpreted as Found with the IF function, while FALSE will be interpreted as Not Found.

**Step 1:** Open up the spreadsheet, remove the old Formula, and replace it with the new one. Be sure to enter the Query and search range properly in the Formula.

.

**Step 2:** Now, you can drag the autofill handle downwards to use more queries and apply the formulas to the rest of the cells.

**Excel Find Function**

Excel’s FIND function may retrieve the location of a particular character or substring inside a text string. This information can be utilized for a variety of purposes.

The following is an example of the syntax for the Find function in Excel:

- FIND(find text, within the text, [start num]) is the command you need to use.
- The first two arguments must be provided, whereas the third may be provided if desired.
- Find text is the name of the character or substring that should be searched for.
- The text string that will be searched inside is denoted by the within text variable. The string is often entered as a cell reference; however, you have the option of typing it straight into the formula if you prefer.
- Start num is a non-mandatory input that provides the search’s starting point from a given character. The search will begin with the first character within a text string if it is not provided.
- If the FIND function cannot locate the find text character(s), it will produce an error with the #VALUE! Prefix.
- For instance, the value 4 is returned by the formula =FIND(“d”, “find”) since the letter “d” appears fourth in the word “find.” Because there is no letter “a” in the word “found,” the calculation =FIND(“a,” “find”) produces an error.

**Step 1:** Open a new MS Excel document, enter the data, and apply the formula. For example, we will use the formula =FIND(“e”, A2) and press enter.

**Step 2:** The formula will return a number, which means it found the letter at the place of the second letter.

**Note:** The FIND function pays attention to the case while searching. Use the SEARCH feature if you are searching for a match that does not care about the case of the words.

- Excel’s FIND function does not support wildcard characters in any shape or form.
- The FIND function will only return the location of the first character in the find text argument if that parameter comprises several characters. For instance, the value 2 is returned by the formula FIND(“ap”, “happy”) since the letter “a” comes after the letter “p” in the word “happy.”
- If there are many instances of finding text contained within text, just the first instance will be returned. For instance, the expression FIND(“l,” “hello”) produces the value 3, which corresponds to the beginning of the “l” letter in the word “hello.”
- The Excel FIND formula will return the first character in the search string if the find text is an empty string (“).
- If any of the following conditions are met, Excel’s FIND function will respond with the #VALUE! Error:
- There is no such thing as finding text within text.
- The value of start num includes more characters than the value of within text.
- The start num is 0 (zero), which means nothing, or a negative value.

## Conclusion

Now we know quite a few ways of excel is a value in a list. This will help us in searching large data sheets in no time. This will cut your work time and make it more efficient. You easily look for multiple queries with the help of these formulas, tips, and tricks that we showed you. Just remember to double-check the formulas and the data ranges in them.