Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Array formula to nest several optional values from same cell [Closed]

Report
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
 -
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
 -
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
See more

6 replies

Answer 1 / 6

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
Answer 2 / 6
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
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.
Answer 3 / 6
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
 1
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
Answer 4 / 6
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
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.
Report
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
 1
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")
Report
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
It enables you to see the result of part of your formula, so why are you trying to drag it down!!!
Report
Blocked Profile
@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)
Answer 5 / 6
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
 1
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
Report
Blocked Profile
It realy sounds to me like you knew the answer all alone. I sure am glad I helped talk you through it.
Answer 6 / 6
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
 1
Not quite, but you certainly helped me. Thanks mate
Ask a question