Excel get cell value which is NOT Match in a row

Question

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?

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, 2012 at 15:08
You can click the tag and check out the questions located there — Oskar Kjellin, Mar 5, 2012 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, 2012 at 15:16

JMax · Accepted Answer · 2012-03-05 14:52:28Z

6

Try:

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

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

answered Mar 5, 2012 at 14:52

JMax

3,2151 gold badge23 silver badges20 bronze badges

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, 2012 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, 2012 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, 2012 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, 2012 at 15:26
You're welcome. Sorry, I didn't add that you had to remove the {}
– JMax
Mar 5, 2012 at 15:45

Add a comment |

Jerry Beaucaire · Accepted Answer · 2013-02-10 14:05:49Z

4

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)

answered Mar 10, 2012 at 11:01

Jerry Beaucaire

5152 silver badges7 bronze badges

Add a comment |

2 Answers 2