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
- Vba check if value is in array - Guide
- Credit summation formula - Guide
- Excel grade 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!!!
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
