a_faulding
New Member
- Joined
- Jul 14, 2020
- Messages
- 8
- Office Version
- 365
Hello,
I am building a spreadsheet and want to check whether a value in an array exists within a text string. If the value within the array does exist within the text string, I would like to return the array value.
For example:
If possible, I would also like to be able to do this with a multi-column array, e.g:
And finally...
Could I do this by referencing a named table instead of a selection, so if new values get added to the bottom of the table, the formula automatically references the full table?
Thanks!
Andrew
I am building a spreadsheet and want to check whether a value in an array exists within a text string. If the value within the array does exist within the text string, I would like to return the array value.
For example:
String | Desired Result | Array Column 1 | |
Tasty Apples | Apple | Apple | |
Pomegranate | - | Banana | |
Orange Peel | Orange | Pear | |
123Pear | Pear | Orange | |
Pineapple | - | Grape |
If possible, I would also like to be able to do this with a multi-column array, e.g:
String | Desired Result | Array Column 1 | Array Column 2 | Array Column 3 | |
Tasty Apples | Apple | Banana | Orange | Grape | |
Pomegranate | - | Apple | Mango | Strawberry | |
Orange | Orange | Mango | Grape | Mango | |
123Pear | Pear | Grape | Strawberry | Apple | |
Pineapple | - | Strawberry | Mango | Pear |
And finally...
Could I do this by referencing a named table instead of a selection, so if new values get added to the bottom of the table, the formula automatically references the full table?
Thanks!
Andrew