Formula to get/find require text in given cell

[harinsh]

Hi Team,

I hope anyone can help with my question. I have two sheets and in the second sheet from A1 to A10 have some names and this list, not static and dynamic as and when list may increase.

In sheet one from A1 to A1000 (this may increase day by day) has fields which contain sheet two names along with some other information which is not required, I only need to get the same name if that particular name within the field.

For example,
Sheet2

apple

mango

orange

banana

kiwi

graps

pinaple

chikku

watermealon

muskmealon Sheet1 - along with a required output Text Output PO4743apple43466 apple Return2982mango43466 mango PO1358orange43466 orange Puchase4290banana43466 banana Stockin2285kiwi43466 kiwi Stockout4299graps43466 graps PO2994pinaple43466 pinaple Return2250chikku43466 chikku Best1538watermealon43466 watermealon Less1476muskmealon43466 muskmealon <tbody> </tbody>

<tbody>
</tbody>

I hope this can be done in the formula, please help on the same.


Thank you

 

[Mackers]

Hi

Try this, using control+shift+enter, for your fruits in Sheet2, with your text in A3 on Sheet1:

=INDEX(Sheet2!$A$2:$A$11,MATCH(TRUE,ISNUMBER(FIND(Sheet2!$A$2:$A$11,A3)),0))

 

[nithin shettigar]

A B C D
1 Text Output Output Criteria
2 PO4743apple43466 apple apple
3 Return2982mango43466 mango mango
4 PO1358orange43466 orange orange
5 Puchase4290banana43466 banana banana
6 Stockin2285kiwi43466 kiwi kiwi
7 Stockout4299graps43466 graps graps
8 PO2994pinaple43466 pinaple pinaple
9 Return2250chikku43466 chikku chikku
10 Best1538watermealon43466 watermealon watermealon
11 Less1476muskmealon43466 muskmealon muskmealon

Formula - =LOOKUP(2^15,SEARCH($D$2:$D$11,A2),$D$2:$D$11) Paste formula in B2

 

[harinsh]

Hi Mackers,

Thanks for your reply...Its working fine but array formula slow in the calculation as I have 35000 (may go up to 100000) rows ....anything without array formula ...

 

[harinsh]

Hi Nithin,

Thanks for your reply... it is working perfect ..can you please tell me how to read 2^15 ...How does exactly works this? Thanks again

 

[nithin shettigar]

Total number of characters that a cell can contain (2^15) = 32768 (Its noting but cell limit)