Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

screen shot

I would like to search C2:I2 which is the cell value NOT equal to "=No=", which is "ABC", and the formula I would like to store in A2.

I was able to look for some formula which works with MATCH(), but I would like to get NOT MATCH.

Can anyone can help in this?

share|improve this question

migrated from Mar 8 '12 at 17:14

This question came from our site for professional and enthusiast programmers.

sorry for the wrong place, I saw similar Excel question asking here. So I posted my question here. Sorry for this. What is the Excel tag means in stackoverflow is it Excel VBA? – Shiro Mar 5 '12 at 15:08
You can click the tag and check out the questions located there – Oskar Kjellin Mar 5 '12 at 15:11
It is true that some questions can or could be answered with either formula or VBA. This is sometimes tricky to know wether to post your question. Don't worry, if needed, your question will be migrated. – JMax Mar 5 '12 at 15:16
up vote 6 down vote accepted



This is an array formula you should validate with Ctrl + Shift + Enter

share|improve this answer
Can you explain more about this formula means? Are you sure is C2:I2<>"A" ? I need not equal to =NO=. And I tried with your formula. It does not work as well. – Shiro Mar 5 '12 at 15:10
sorry. I made my test with only A and B (because "=" is a pain to handle in an Excel cell). Btw, I also corrected because I forgot to translate the TRUE statement. – JMax Mar 5 '12 at 15:13
the formula is trying to Match within an array if any of the cell is different from "=NO=" by checking if it returns TRUE – JMax Mar 5 '12 at 15:14
Thank JMax. This is the first time I used array formula, I didn't notice that I shouldn't include the {} :p Thank you so much! – Shiro Mar 5 '12 at 15:26
You're welcome. Sorry, I didn't add that you had to remove the {} – JMax Mar 5 '12 at 15:45

Here's a non-array suggestion:

enter image description here

And here is the formula you can copy paste:

=LOOKUP(2, 1/($C$2:$J$2<>"=No="), $C$2:$J$2)

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .