Previous Topic Next Topic

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

Answer 1 / 6
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
Answer 2 / 6
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
Answer 3 / 6
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
Answer 4 / 6
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
Answer 5 / 6
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
Answer 6 / 6
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