#1




searching a small text in a bigger text using VLOOKUP
Greetings,
I want to search a portion of a string from a longer string using VLOOKUP. I am using VLOOKUP formula but it fails to provide a needed answer. I attach an example so that you can help me to generate a correct result. Thank you 
#2




You'll struggle with VLookUp.
In the attached are two solutions because in your examples all the smaller strings are to be found at the beginning of the longer strings, but I've included a formula to look the smaller strings anywhere within the longer strings. In column B is the formula to find the smaller strings anywhere within the longer strings: In cell B1: =IFERROR(INDEX(Sheet1!$B$1:$B$4,MATCH("*" & A1 & "*",Sheet1!$A$1:$A$4,0)),"") copied down. Note that cell B8 contains a value because it's looking for an empty string and finds it in the row 1. To find the smaller strings only at the beginning of the longer strings, in C1 arrayenter the following (arrayentering means committing the formula to thesheet with Ctrl+Xhift+Enter, not just Enter): =IFERROR(INDEX(Sheet1!$B$1:$B$4,MATCH(A1,LEFT(Shee t1!$A$1:$A$4,LEN(A1)),0)),"") and copy down. 
#3




Thank you very much, the formula is working on some strings bunt not on other strings. I attach examples where the formula is returning a blank cell instead of returning written result

#4




You need to include the extra rows you're returning:
=INDEX(Sheet1!$B$1:$B$4,MATCH("*" & A10 & "*",Sheet1!$A$1:$A$8,0)) needs to be: =INDEX(Sheet1!$B$1:$B$8,MATCH("*" & A10 & "*",Sheet1!$A$1:$A$8,0)) 
#5




Thank you for your support but the problem still exists. I attach a full string, so that a problem can be handled once for all.
Sorry for returning 
#6




In B1:
Code:
=IFERROR(INDEX(Sheet1!$B$1:$B$8,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A1,Sheet1!$A$1:$A$8)),0),0)),"") If the smaller strings you're looking for ever exceed 255 characters and it fails, comeback for another (longer) solution. 
#7




Thank you, you helped me. God bless you
the formula works 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Text display size is too small  jim999  PowerPoint  1  01022015 02:55 AM 
Animation "get bigger" means low quality of my text!!!!  LillAlbyl  PowerPoint  1  12042012 11:14 AM 
searching for text in multiple excel files  roytom  Excel  2  07302012 01:57 PM 
Removing small bluedotted text box  EuroOffice  Word  1  05082012 05:43 PM 
How to deactivate small images of text  Robert E  Drawing and Graphics  9  12162009 04:01 PM 