Why won't my 'OR' formula work for all of my rows?

**stell64** · 09-21-2018, 05:18 PM

This seems so simple but I can't figure it out.

I am trying to use the OR function with an array. My formula reads =OR(E2='Freight Forward'!$A$2:$A$28)

Basically i'm trying to check if the text in a cell (E2) is equal to any of the values in the A column of the sheet named 'Freight Forward'.

When I try and drag that formula to auto-fill the whole column, it only works for the first 28 rows and then I start getting #VALUE! returned. I thought the $ symbols would keep the reference to the range from moving and it would auto-fill my whole column, what am I doing wrong?

Thanks.

**¯\_(ツ)_/¯** · 09-21-2018, 05:27 PM

Could you use this instead?

=ISNUMBER(MATCH(E2,'Freight Forward'!$A$2:$A$28,0))

Should return TRUE or FALSE.

**stell64** · 09-21-2018, 05:36 PM

Worked!

Thank you so much. Any idea why 'OR' wasn't working? It was driving me crazy.

**¯\_(ツ)_/¯** · 09-21-2018, 05:53 PM

The OR function you have should work as long as you 'array-enter' the formula into the cell (and then fill down/up/etc. as needed). Array-entering a formula requires using CTRL+SHIFT+ENTER, not just ENTER. If you edit the formula in any array-entered cell, you must remember to confirm it using C+S+E again.

When done properly, an array-entered formula will have braces { } around it.

Why won't my 'OR' formula work for all of my rows?

LinkBack

Thread Tools

Rate This Thread

Display

Why won't my 'OR' formula work for all of my rows?

Re: Why won't my 'OR' formula work for all of my rows?

Re: Why won't my 'OR' formula work for all of my rows?

Re: Why won't my 'OR' formula work for all of my rows?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Why does Index and Match Formula work for Named Range.. Except The Last Rows?

Formula work when i use Subtotal(5, but wont work when i use Min. Y? WHats the difference?

[SOLVED] Need a COUNTIF formula to work with Excel 2000. Not confident it will work?

[SOLVED] How to make the macro work for all rows in the work sheet

Time Sheet with Formula cell to work out the hours work

Copy rows from work sheets to combine to one work sheet based on cell value

[SOLVED] If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct?

Bookmarks

Bookmarks

Posting Permissions