I have a two named ranges, user input Profiles (vertical range of one column) and pre-determined ProfileNames (horizontal range of one row). I have a data validation step in my tool, and would like to check if any values input to Profiles are not found in ProfileNames. I'm trying to find out a way to do this in one formula but I seemed to be stumped.

Currently, this is achieved like this: each cell in ProfileNames is its own Name (Profile1, Profile2, etc.). Then this formula is used:

COUNTIFS(Profiles,"<>"&Profile1, Profiles,"<>"&Profile2, ...)

with anything greater than 0 triggering a popup. I'd like to move past this, as there will eventually be 200+ profiles to check and this formula is unwieldy. I need a formula to replace the above one which will detect any cells in Profiles that do not match at least one cell in ProfileNames.

Sample data:

Cooling | Heating | Cooking 1 .5 .75

The first row above (Cooling:Cooking) is ProfileNames "Cooling" is Profile1, Heating is Profile2, etc. This column is Profiles:

Cooling Cooling Cooking Heating Heating

I'm looking to avoid using Profile1 etc as there will be 200+ of these and the formula being used now may expand to exceed maximum character limits.

  • Could you please some sample data and describe exactly how do you work? It's not clear now. Jan 17, 2017 at 19:01
  • @MátéJuhász edited to add sample Jan 17, 2017 at 21:56

1 Answer 1


You can use an array formula to check. Type

=SUM((Profiles = ProfileNames) * 1)

into a cell, but instead of using enter press control-shift-enter. The result is the number of entries in Profiles that match an entry in ProfileNames. Therefore, you know that if there are e.g. 5 entries in Profiles and the sum is 5, all are included in ProfileNames. If the sum is less than 5, one of the entries in Profiles isn't correct.

Explanation: Profiles = ProfileNames creates a matrix of TRUEs and FALSEs corresponding to checking each row in Profiles against each column in ProfileNames. The * 1 turns that matrix into a matrix of numbers, and SUM sums them up.

  • Thanks, I was hoping to avoid using an array formula but it seems to be the only way. I ended up scrapping this part of the tool anyway. Jan 20, 2017 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.