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

6 replies

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
0
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.
0
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
0
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.
0
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")
0
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!!!
0
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)
0

Didn't find the answer you are looking for?

Ask a question
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
Apr 13, 2014 at 12:37 PM
Hi Kevin,

I know what you mean and it was probably me not explaining it properly. I came across array formula =IF(OR(AL2362={"waste","Shunt","GKN","no","BOS","85"}),"1"). Thanks for your time sorry not explaining it clearly. It can be complex sometimes to explain excel issues I guess
0
Blocked Profile
Apr 13, 2014 at 02:49 PM
It realy sounds to me like you knew the answer all alone. I sure am glad I helped talk you through it.
0
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
Apr 13, 2014 at 03:00 PM
Not quite, but you certainly helped me. Thanks mate
0