Comparing Text to Several Cells

[Pontus]

I am trying to compare the word in one specific cell to the word in several
other cells in a single column. The purpose is that the cell were the code is
should display a specific word if the comparision is equal, if not it should
display the word in the specific cell you want to compare with the column.

I have managed to do this but in the way I do it I have to write a hell of a
lot of code since it is many cells that the specific cell should be compared
to. This is probably easily solved, or at least it feals like it should be
easily solved if you know the program a bit better. The code I use is:


=IF(OR(I6=B4;I6=B5;I6=B6;I6=B7;I6=B8;I6=B9;I6=B10;I6=B11;I6=B12);B2;I6)


As you see I now compare cell "I6" with cells B4 to B12 why the code isn't
redicolously long but I would like to compare to many more cells and then it
will be. My initial fealing was to write B4:B12 but this gave the error
"#value" which is natural since ":" make a sum but it is something similar I
would like to do, short and simple.

If anybody has a solution to this I would be highly grateful.

thanks
Pontus

 

[Ed Ferrero]

Hi Pontus,

Try
=ISNUMBER(MATCH(I6,$B$4:$B$39,0))

Ed Ferrero
www.edferrero.com

 

[Ed Ferrero]

Or, closer to what you actually asked for...

=IF(ISNUMBER(MATCH(I6,$B$4:$B$39,0)),B2,I6)

Ed Ferrero
www.edferrero.com

 

[Pontus]

Hi Ed,

Thank you very much for your help. This looks like a really good solution
which I really like. However, I can not get "MATCH" to function properly. I
tried to read up on it and in the help it says that it should function with
text (which I use) but I only get #N/A value. This is what "MATCH" is suposed
to return when it cannot find a match.

Since I always get #N/A, "ISNUMBER" always returns false and you understand
that it wont give the result I want.

Do you have any idea to why this is so??

By the way, I don't know if it is because of an old version but I have to
exchange all the ":" to ";" to seperate the values in the function. Maybe
thats nothing strange.

//Pontus

 

[Pontus]

Hi Ed,

I'm sorry, it did function =D In some of my texts I had ended with a blank
space while the "look-up value" was not so that why it didn't function in the
biggining. I guess one have to be a bit careful when one type in or
copy-paste so that it doesn't end with a blank space.

Thank you very much for your help, this was the first time I posed a Q here
and I am really glad you helped me =)

Take care and have a nice weekend!
//a very grateful Pontus