Hi,
this has been driving me crazy and i am unable to find a formula to drive me the result. Please refer to the below case.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]234563; 234575; 564543[/TD]
[TD]Apple[/TD]
[TD]Yes[/TD]
[TD]234575[/TD]
[TD](Apple)[/TD]
[/TR]
[TR]
[TD]223344; 278923; 232324[/TD]
[TD]mango[/TD]
[TD]No[/TD]
[TD]232324[/TD]
[TD](Mango)[/TD]
[/TR]
[TR]
[TD]123456; 567890[/TD]
[TD]banana[/TD]
[TD]Yes[/TD]
[TD]123456[/TD]
[TD](Banana)[/TD]
[/TR]
</tbody>[/TABLE]
How can get the values in column E i.e. apple, mango and banana using D1 as the lookup array. First challenge is to derive the value in D from cell A and then lookup.
Please note that split cells wont work as there can be instances where i could have more than 200 numbers in a cell.
Appreciate your swift response on this.
Regards,
Ankit Jain
this has been driving me crazy and i am unable to find a formula to drive me the result. Please refer to the below case.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]234563; 234575; 564543[/TD]
[TD]Apple[/TD]
[TD]Yes[/TD]
[TD]234575[/TD]
[TD](Apple)[/TD]
[/TR]
[TR]
[TD]223344; 278923; 232324[/TD]
[TD]mango[/TD]
[TD]No[/TD]
[TD]232324[/TD]
[TD](Mango)[/TD]
[/TR]
[TR]
[TD]123456; 567890[/TD]
[TD]banana[/TD]
[TD]Yes[/TD]
[TD]123456[/TD]
[TD](Banana)[/TD]
[/TR]
</tbody>[/TABLE]
How can get the values in column E i.e. apple, mango and banana using D1 as the lookup array. First challenge is to derive the value in D from cell A and then lookup.
Please note that split cells wont work as there can be instances where i could have more than 200 numbers in a cell.
Appreciate your swift response on this.
Regards,
Ankit Jain