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
jutulu Posts 34 Registration date Monday March 10, 2014 Status Member Last seen September 21, 2015 - Apr 13, 2014 at 03:00 PM
Related:
- Array formula to nest several optional values from same cell
- Number to words in excel formula - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel grade formula - Guide
- Vba check if value is in array - Guide
- Credit summation formula - Guide
6 responses
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
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
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.
=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
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
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
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")
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
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!!!
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
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
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
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
Apr 13, 2014 at 03:00 PM
Apr 13, 2014 at 03:00 PM
Not quite, but you certainly helped me. Thanks mate
