# Array formula to nest several optional values from same cell

Closed
jutulu Posts 34 Registration date Monday March 10, 2014 Status Member Last seen September 21, 2015 - Apr 4, 2014 at 05:52 PM
jutulu Posts 34 Registration date Monday March 10, 2014 Status Member Last seen September 21, 2015 - Apr 13, 2014 at 03:00 PM
Hi,

All the OR statement formula below refers to the same cell AL2362. How could I make an array formula to put all the OR values in one without repeating AL2362= every time?

=IFORAL2362="waste",AL2362="Shunt",AL2362="GKN",AL2362="no",AL2362="BOS",AL2362="85"),"1")

Thank you
Related:

## 6 responses

Blocked Profile
Apr 4, 2014 at 06:00 PM
Ok, the easiest thing to do, is name the cell a range.

Then in excel, evertime you use the variable of "TIRES", it refers to cellAL2362.

Give that a go!

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Apr 5, 2014 at 03:37 AM
Perhaps something along these lines.

=IF(ISNUMBER(LOOKUP(20^20,SEARCH(T1:T6,AL2362))),1,0)

Where T1:T6 holds your list of names/numbers.
jutulu Posts 34 Registration date Monday March 10, 2014 Status Member Last seen September 21, 2015 2
Apr 6, 2014 at 05:31 PM
Thank you. Will try it and let you know. However, can you pls explain me 20^20,SEARCH(T1:T6,AL2362))),1,0), specially the 20^20 with the symbol ^? I suppose 1,0 is to avoid it to give you a FALSE statement if not true. Many thanks
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Apr 10, 2014 at 12:18 AM
The 20^20 is a large number. The Search is looking for a match in AL2362 from your list T1:T6. So if you select the cell where your formula is > F2 on the keyboard > Left click and drag across the search function and hit the F9 key this display an array if there is a match there will be a 1 in the array. So the lookup is looking for a number and returns the number 1.
jutulu Posts 34 Registration date Monday March 10, 2014 Status Member Last seen September 21, 2015 2
Apr 12, 2014 at 08:09 PM
Hi Kevin,

Thank for the above. But, I don't understand very well the part where you ask me to select the cell where your formula is > F2 on the keyboard > Left click and drag across the search function and hit the F9 key. It seams to work fine but only on the first column, then it only shows number 1. I pressed function F2 on the formula, and then dragged it down....but not clear what you mean by drag across the search function and hit the F9 function. Once I dragged it down (sorry, but as said not sure what you mean by dragging it across the search function), and then hit F9, but nothing happens. Many thanks

=IFORAL2362="waste",AL2362="Shunt",AL2362="GKN",AL2362="no",AL2362="BOS",AL2362="85"),"1")
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Apr 13, 2014 at 01:16 AM
It enables you to see the result of part of your formula, so why are you trying to drag it down!!!
Blocked Profile
Apr 13, 2014 at 12:03 PM
@jutulu- it returns 1 as you are telling it to return 1. BTW naming the cell tires works also....
=IF(SEARCH(D15:D18,TIRES),1,0)

Didn't find the answer you are looking for?