Find partial match between two columns and return a third column

Question

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?

pnuts · Accepted Answer · 2015-03-31 10:24:42Z

1

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.

answered Mar 31, 2015 at 10:24

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BrakNicku · Accepted Answer · 2015-03-31 10:24:35Z

0

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

answered Mar 31, 2015 at 10:24

BrakNicku

6,0313 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

Find partial match between two columns and return a third column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related