troubleshooting Question

# Excel formula to determine if a given value is included in a defined range of cells

Microsoft OfficeMicrosoft Excel
Hello,

What is an Excel formula that will tell you if the adjacent cell contains a value which is included in a defined range of values?

For example, suppose you have a range of cells (\$F\$2:\$F\$52) which contains 50 unique values. And suppose in column A, you have a list of several thousand values.

What formula in column B will tell you if the adjacent value in column A is one of the 50 unique values in column F?

I suppose you could accomplish it by using =VLOOKUP() in column B:

You would first need to place the value "Yes" in cells G2:G52, then use the (50 row x 2 column) range \$F\$2:\$G\$52. The resulting formula would be:

``````=VLOOKUP(A2,\$F\$2:\$G\$52,2,0)
``````
and the result in each row would be "Yes" or #N/A.

However, it seems like there should be a more direct formula which does not require the values in column G. Is that correct or is using the =VLOOKUP() method as good as any?

Thanks
Ejgil Hedegaard
###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.