Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com 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
add comment

2 Answers 2

up vote 5 down vote accepted

Try:

{=INDEX(C2:I2,MATCH(TRUE,C2:I2<>"=NO=",0))}

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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