In Column G, I have got sentences for example
G1 I like Bananas
G2 I like Apples
G3 I like Grapes
In Column M, I have got the following formula
=INDEX($T$1:$T$300,MATCH("*"&LOOKUP(9.99999999999999E+307,SEARCH(List,G1),List)&"*",$S$1:$S$300,0))
This formula is filled down to last row of data of Column G. The 300 number represents the number of rows in S.
In Column S, I have types of fruits for example
S1 Apples,grapefruit
S2 Grapes,tomatos
S3 Bananana
In Column T, I have values for example
T1 1
T2 2
T3 3
In a separate sheet (same workbook), I have all possible fruits in Column E. I have defined the list name as list.
For the formula in Column M to work correctly, I would like to search column G for a fruit, Lookup that fruit in Column S and than return whatever value is in the same row for Column T.
The return for M1 should be 3
The return for M2 should be 1
The return for M3 should be 2
For some reason either a 1 or #N/A shows up in the M Column. Does this make sense?
G1 I like Bananas
G2 I like Apples
G3 I like Grapes
In Column M, I have got the following formula
=INDEX($T$1:$T$300,MATCH("*"&LOOKUP(9.99999999999999E+307,SEARCH(List,G1),List)&"*",$S$1:$S$300,0))
This formula is filled down to last row of data of Column G. The 300 number represents the number of rows in S.
In Column S, I have types of fruits for example
S1 Apples,grapefruit
S2 Grapes,tomatos
S3 Bananana
In Column T, I have values for example
T1 1
T2 2
T3 3
In a separate sheet (same workbook), I have all possible fruits in Column E. I have defined the list name as list.
For the formula in Column M to work correctly, I would like to search column G for a fruit, Lookup that fruit in Column S and than return whatever value is in the same row for Column T.
The return for M1 should be 3
The return for M2 should be 1
The return for M3 should be 2
For some reason either a 1 or #N/A shows up in the M Column. Does this make sense?
