CODEWITHSUNDEEP
excelmatchformulamatchingpartial
user3374823
user3374823

Reputation: 1

Find partial match between two columns and return a third column

I'd like to create a formula that looks for a partial match in two columns in two different Worksheets, and if there is a partial match between the two columns, it returns the value of a different column in Worksheet 2.

Example:

Sheet 1 Column A:
Dev
Tim
Jon

Sheet 2 Column A:
Development
Timothy
Jonathan

If there is a partial match as there is in these fields, I want it to return the value from Sheet 2 Column B.

Currently I have the following formula:

=INDEX(ValueIWant,MATCH(A1,ColumntoPartialMatchAgainst,0))

However, this returns the "ValueIWant" field only when there is an exact match.

How can I modify this formula to work with even a partial match?

Upvotes: 0

Views: 18566

Answers (2)

pnuts
pnuts

Reputation: 59475

Please try: 

=VLOOKUP(A1&"*",`Sheet 2`!A:B,2,0)

Note this deliberately only adds a wildcard (*) to the end of the Sheet 1 values to reduce the risk of a match with something like estimate.

Upvotes: 1

BrakNicku
BrakNicku

Reputation: 5991

To achieve the desired result you should concatenate wildcard(s) to the value to search. In your formula, instead of A1, please try:

A1 & "*" - if you want to match only the beginning of array values (like in your sample data)

"*" & A1 & "* - to match any part of array values

Upvotes: 0

Related Questions