asked on

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

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!!!

jppinto

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

jppinto

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

jppinto
Lookup-Values.xlsx

glabossi

ASKER

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)

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

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

byundt

I was using jppinto's sample workbook to test the formula

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial