Solved

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

Posted on 2011-05-10
Medium Priority
549 Views
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
Question by:glabossi

LVL 33

Expert Comment

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

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

jppinto
Lookup-Values.xlsx
0

Author Comment

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

LVL 33

Accepted Solution

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

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

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

LVL 81

Assisted Solution

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

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

## Featured Post

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.
###### Suggested Courses
Course of the Month8 days, 13 hours left to enroll