3
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?
Shiro
Posted 2012-03-05T09:45:43.130
Reputation: 619
6
Try:
{=INDEX(C2:I2,MATCH(TRUE,C2:I2<>"=NO=",0))}
This is an array formula you should validate with Ctrl + Shift + Enter
JMax
Posted 2012-03-05T09:45:43.130
Reputation: 3 005
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 – 2012-03-05T15:10:35.990
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 – 2012-03-05T15:13:49.423
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 – 2012-03-05T15:14:51.670
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 – 2012-03-05T15:26:26.993
You're welcome. Sorry, I didn't add that you had to remove the {} – JMax – 2012-03-05T15:45:19.383
4
Here's a non-array suggestion:
And here is the formula you can copy paste:
=LOOKUP(2, 1/($C$2:$J$2<>"=No="), $C$2:$J$2)
Jerry Beaucaire
Posted 2012-03-05T09:45:43.130
Reputation: 495
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 – 2012-03-05T15:08:24.070
You can click the tag and check out the questions located there – Oskar Kjellin – 2012-03-05T15:11:42.910
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 – 2012-03-05T15:16:07.677