'Identify all instances of a string in an array and return the row above
Identify all instances of a string in an array and return the rows above.
Using INDEX/MATCH combo only identifies the first instance in the array.
Solution 1:[1]
Yep - the lookup/index/match functions seek one instance only.
If this is a tickbox-style problem, the array of 'options' is small, and you just need a block/chunk of text including all the 'options selected,' consider using IF() and CONCAT():
| Responses | val1 | val2 | val3 | summary |
|---|---|---|---|---|
| response 1 | x | x | =CONCAT(IF(B2="x","val1 ",""), IF(C2="x","val2 ",""), IF(D2="x","val3 ","")) |
If you really need "row above", substitute the row above for the value strings (instead of val1, use B1, etc.).
It's not pretty data in the summary cell ... but maybe this will help you move ahead.
If the string is inside another string (like a block of text or something), the IF() test will be more complicated but this approach should still work.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Denton Thomas |