GOAL B: Define continuous named range (like A1:A25) based on keyword criteria. (provided that user takes care to make sure keywords are always in 1 cluster.) (with a formula)
Solution by : XOR LX (Though you'll probbaly need to read the thread to grasp it )
=INDEX(Sheet2!$B$1:$B$1000,MATCH(TRUE,INDEX(ISNUMBER(SEARCH("dog",Sheet2!$B$1:$B$1000)),,),0)):INDEX(Sheet2!$B$1:$B$1000,MATCH(2,INDEX(1/ISNUMBER(SEARCH("dog",Sheet2!$B$1:$B$1000)),,)))
What this does is search for "dog" in B1 to B1000 and provides the address of the first match then the last match. So a dynamic named range can be mapped based on the first address of the keyword (dog) and the last address of the keyword. So if B30 to B150 had dog in it - it would be mapped as a named ranged. You can later have the "block" of dog to B220 to B340 and this formula will still keep it mapped properly. Obviously you can offset it if you want the extra flexibility.
TAGS: Return cell addresses based on criteria, return first address of match, return last address of match.
Bookmarks