?
Solved

Excel - Possible to do a Vlookup with Startswith or Contains?

Posted on 2011-05-10
8
Medium Priority
?
549 Views
Last Modified: 2012-05-11
Wanted to know if there is a way to do a Vlookup formula that can match using a startswith or contains?

Account Name Sheet 1       Account Name Sheet 2

ACS Systems                     ACS

From sheet 2, I want to pull ACS Systems from sheet 1 using a startswith to match from ACS on sheet 2.

Is this possible?  Is so, please post the formula?

Thanks!!!
0
Comment
Question by:glabossi
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35731062
You could build a column where you list only the 3 left chars from Account Name so that you can use it on the VLOOKUP() formula.

Can you post a sample sheet? What is the value that you want to retrieve? Text or numbers? If it's numbers, you could use the SUMPRODUCT() function, something like this:

=SUMPRODUCT((LEFT(A1:A2,3)="ACS")*(B1:B2))

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35731097
Please check the attached file to see the two examples working on the yellow cells.

jppinto
Lookup-Values.xlsx
0
 

Author Comment

by:glabossi
ID: 35731175
My end result is text.  

I will have a spreadsheet of account names (around 10K) and need to do a partial match from another spreadsheet.  Your example is on the right track, but it seems manual (Adding the ACS in the formula)- VLOOKUP("ACS",A6:C7,2,FALSE)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 33

Accepted Solution

by:
jppinto earned 668 total points
ID: 35731199
This was just an example! You can change it for something like this:

=VLOOKUP(G6,A6:C7,2,FALSE)

jppinto
0
 
LVL 81

Expert Comment

by:byundt
ID: 35731206
INDEX and MATCH have the capability of using wildcards:
=INDEX(B$1:B$2,MATCH(A6 & "*",A$1:A$2,0))                    'Returns a value from column B if column A begins with the string in cell A6
0
 
LVL 81

Expert Comment

by:byundt
ID: 35731214
I was using jppinto's sample workbook to test the formula
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 664 total points
ID: 35731234
If you want a "contains" criteria, just modify the first parameter of the MATCH accordingly:
=INDEX(Sheet1!B$1:B$200,MATCH("*" & A6 & "*",Sheet1!A$1:A$200,0))                 'Retrieve a value from Sheet1 column B when column A contains the text in cell A6
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 668 total points
ID: 35732665
FYI, VLOOKUP does the same:
=VLOOKUP("ACS*",A1:B2,2,FALSE)
for example.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question