Microsoft Office Forums searching a small text in a bigger text using VLOOKUP

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2019, 03:35 AM
Tesla Tesla is offline searching a small text in a bigger text using VLOOKUP Windows 7 32bit searching a small text in a bigger text using VLOOKUP Office 2003
Novice
searching a small text in a bigger text using VLOOKUP
 
Join Date: Sep 2018
Posts: 24
Tesla is on a distinguished road
Default 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
Attached Files
File Type: xlsx example.xlsx (8.6 KB, 2 views)
Reply With Quote
  #2  
Old 04-04-2019, 04:44 AM
p45cal p45cal is offline searching a small text in a bigger text using VLOOKUP Windows 10 searching a small text in a bigger text using VLOOKUP Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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 array-enter the following (array-entering 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.
Attached Files
File Type: xlsx msofficeforums42148example.xlsx (10.4 KB, 1 views)
Reply With Quote
  #3  
Old 04-04-2019, 06:20 AM
Tesla Tesla is offline searching a small text in a bigger text using VLOOKUP Windows 7 32bit searching a small text in a bigger text using VLOOKUP Office 2003
Novice
searching a small text in a bigger text using VLOOKUP
 
Join Date: Sep 2018
Posts: 24
Tesla is on a distinguished road
Default

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
Attached Files
File Type: xlsx msofficeforums42148example.xlsx (9.8 KB, 2 views)
Reply With Quote
  #4  
Old 04-04-2019, 06:36 AM
p45cal p45cal is offline searching a small text in a bigger text using VLOOKUP Windows 10 searching a small text in a bigger text using VLOOKUP Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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))
Reply With Quote
  #5  
Old 04-04-2019, 07:21 AM
Tesla Tesla is offline searching a small text in a bigger text using VLOOKUP Windows 7 32bit searching a small text in a bigger text using VLOOKUP Office 2003
Novice
searching a small text in a bigger text using VLOOKUP
 
Join Date: Sep 2018
Posts: 24
Tesla is on a distinguished road
Default

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
Attached Files
File Type: xlsx msofficeforums42148example-1.xlsx (10.9 KB, 4 views)
Reply With Quote
  #6  
Old 04-04-2019, 07:53 AM
p45cal p45cal is offline searching a small text in a bigger text using VLOOKUP Windows 10 searching a small text in a bigger text using VLOOKUP Office 2016
Expert
 
Join Date: Apr 2014
Posts: 268
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

In B1:
Code:
=IFERROR(INDEX(Sheet1!$B$1:$B$8,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A1,Sheet1!$A$1:$A$8)),0),0)),"")
A 255 character limit on MATCH with wildcards was breached.
If the smaller strings you're looking for ever exceed 255 characters and it fails, comeback for another (longer) solution.
Reply With Quote
  #7  
Old 04-05-2019, 05:48 AM
Tesla Tesla is offline searching a small text in a bigger text using VLOOKUP Windows 7 32bit searching a small text in a bigger text using VLOOKUP Office 2003
Novice
searching a small text in a bigger text using VLOOKUP
 
Join Date: Sep 2018
Posts: 24
Tesla is on a distinguished road
Default

Thank you, you helped me. God bless you
the formula works
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text display size is too small jim999 PowerPoint 1 01-02-2015 02:55 AM
Animation "get bigger" means low quality of my text!!!! LillAlbyl PowerPoint 1 12-04-2012 11:14 AM
searching a small text in a bigger text using VLOOKUP searching for text in multiple excel files roytom Excel 2 07-30-2012 01:57 PM
searching a small text in a bigger text using VLOOKUP Removing small blue-dotted text box EuroOffice Word 1 05-08-2012 05:43 PM
searching a small text in a bigger text using VLOOKUP How to deactivate small images of text Robert E Drawing and Graphics 9 12-16-2009 04:01 PM


All times are GMT -7. The time now is 09:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft