How to compare multiple cells without using EXACT?

[maocat]

Hi all,

I need to compare multiple cells (not an array) to see if they equal a "specific text", but unsure of how to do that.

I saw a suggestion somewhere which used:

and(exact(A1,B1),exact(B1,C1),exact,(C1,D1),exact(D1,"specific text"))

Which will return true if all is equal. However, I'm hoping to have something simpler if possible.

- cells aren't in an array
- case doesn't matter
- I read somewhere that we need to use CTRL + SHIFT + ENTER to make the exact formula work? I tried the formula without using the Ctrl/shift/enter and it seemed to work.. but maybe I'm not understanding this part correctly. If the exact formula does require this to work properly, I'd prefer to skip the exact formula altogether as this is a template that is being rolled out to 20+ people and I don't want to impose certain conditions on how certain cells are used.

Thank you!

 

[FormR]

- cells aren't in an array

Hi, welcome to the forum! What are the actual cells that need checking?

 

[maocat]

Thank you

They're multiple cells within the same sheet but in the same column.

So R400, R500, R600, etc...

Hi, welcome to the forum! What are the actual cells that need checking?

 

[FormR]

So R400, R500, R600, etc...

Are they always 100 rows apart starting from row 400?

What is in between the cells, anything? Can the "specific text" appear between?

How many cells need checking?

 

[maocat]

There aren't always 100 rows in between, I thought it would be easier for the example. At the moment, the cells in my worksheet are:

R263, R864, R866

I'm checking 3 cells right now, 4 at most (so far).
Between those cells, there are lots of other values showing up, and yes the "special text" can appear between, which I do not want to take into consideration.

So I'm hoping to find R263 = R864 = R866 = "special text".

Also, with the layout of the template, the results need to be returned within the same column, so I would be typing this formula into R870.

I'm not sure if this makes a difference, but the same check will need to be done further on down the page.

So independent of the first set of checks, my second check is:

R1000 = R1009 = R1112 = "special text #2 ", and the result needs to be returned to R1114

Sorry, I know I could probably show a better example via the software but my work computer doesn't allow the download! :/

Are they always 100 rows apart starting from row 400?

What is in between the cells, anything? Can the "specific text" appear between?

How many cells need checking?

 

[FormR]

R263, R864, R866

I'm checking 3 cells right now, 4 at most (so far).

Hi, in that case, I think this would work for you and not be too cumbersome.

=AND(R263="Specific Text", R864="Specific Text", R866="Specific Text")

 

[maocat]

Ahhh okay! That works, I'll use that.

Though, If i had to compare a lot more cells (10+), then I'll start getting worried but I guess I'll worry about that if that time comes !

THank you!

Hi, in that case, I think this would work for you and not be too cumbersome.

=AND(R263="Specific Text", R864="Specific Text", R866="Specific Text")

 

[FormR]

Though, If i had to compare a lot more cells (10+), then I'll start getting worried but I guess I'll worry about that if that time comes !

It could start to get un-managable quite quickly - but alternatives might not prove easy to come by if there is no logical way to determine which cells need checking.