Sometimes when working with a list, you would just like to know if a certain value appears.
By once again using an array, we can test for this very quickly.
The exercise and the result are in the file called Value Appears.
In this example, we will see if we have a match on a company name. Here is the layout of the data.
Next we add in a section where someone can type in the name of a company and we type in one company name to test.
Now we add in a section for doing the test and displaying if there is a match.
The formula is {=OR(C3=company)}
Let's break it down:
company- we use a named range called "company" to cover all the company names
C3- where the value is that we want to test
C3=company- we will test every line in company to see if there is a match, by using an array
=OR(C3=company)- we use the OR function to do a test. All we need is one match in whole range for OR to be true, otherwise, it is false.
{=OR(C3=company)}- we put the closing brackets around the formula by clicking Ctrl, Shift and Enter together to enter the formula as we do for any array
The drawback- it has to be an exact match. If we typed in Kearney, we would not get a match. There is a simple way to do it through the VBA code, which is an advanced topic. We have not figured out a way to do it just using the Excel functions and an array. (We have not given up yet).
Let's go a step further. Let's also point out the first row where there is a match. You may recall a formula that would work from a reason lesson on the MATCH function, which returns the row number for a match.
=MATCH(C3,company,0)
This formula would work when there is a match. Remember the MATCH syntax:
What value are you trying to match
What range are you testing
What type of match (0=exact)
However, if there is no match, then the formula would trigger an ISNA error. Fortunately, we reviewed how to fix that possible error. With that in mind, here is the complete result to show the row number:
=IF(ISNA(MATCH(C3,company,0)),"",MATCH(C3,company,0))
Again, let's break down the formula:
MATCH(C3,company,0)- as mentioned above, we find the row number of a match
=IF(ISNA(MATCH(C3,company,0))- we do an IF test and see of the match formula generates an error
""- if there is an error, then we return a blank value for the true part of the IF test
if there is no error, the false part of the error test, then we go ahead and display the match
While you may not remember all this at your fingertips, by being a member of FLA, you have access to these formulas from anywhere you have web access. Find this from our Excel Tips Index page and then copy the formulas when you need them!