Array formula to nest several optional values from same cell
Closed
jutulu
jutulu
- Posts
- 34
- Registration date
- Monday March 10, 2014
- Status
- Member
- Last seen
- September 21, 2015
jutulu
- Posts
- 34
- Registration date
- Monday March 10, 2014
- Status
- Member
- Last seen
- September 21, 2015
Related:
- Array formula to nest several optional values from same cell
- Formula to copy and paste values in excel automatically - Guide
- Can the formula COUNTA / COUNT exclude 0 values? ✓ - Forum - Excel
- IF function formula based on three possible values from adjacent ✓ - Forum - Excel
- How do i change the result of a formula to normal text or values ✓ - Forum - Excel
- Where are the options for formatting a cell's date to an international format found ✓ - Forum - Excel
6 replies
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
Apr 5, 2014 at 03:37 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
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
Apr 6, 2014 at 05:31 PM
- Posts
- 34
- Registration date
- Monday March 10, 2014
- Status
- Member
- Last seen
- September 21, 2015
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
Apr 10, 2014 at 12:18 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
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
Apr 12, 2014 at 08:09 PM
- Posts
- 34
- Registration date
- Monday March 10, 2014
- Status
- Member
- Last seen
- September 21, 2015
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
Apr 13, 2014 at 01:16 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
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
Apr 13, 2014 at 12:37 PM
- Posts
- 34
- Registration date
- Monday March 10, 2014
- Status
- Member
- Last seen
- September 21, 2015
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
Apr 13, 2014 at 03:00 PM
- Posts
- 34
- Registration date
- Monday March 10, 2014
- Status
- Member
- Last seen
- September 21, 2015
Apr 13, 2014 at 03:00 PM
Not quite, but you certainly helped me. Thanks mate