Excel 2007 - How to do vlookup based on partial match

JCJG
JCJG used Ask the Experts™
on
I'd like to assign category based on partial text using a lookup table.  Please see the attached file.  In the desc column in the Data tab contain text I want to look up.  For example, any line contain the text "ABC" will be assigned to "Category 1" (see table on the lookup tab).  I assume it can be done with formulas.

In the future, I might add an account column next to desc and expand the lookup table with an account column.  Then I would like to lookup categories based on text AND account.  Is this doable?

Thanks!
Excel---Map-text-to-category.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
You can use LOOKUP/FIND combination in an array formula to solve this:

[B2]=LOOKUP(1,1/(IFERROR(IF(FIND(Lookup!$A$2:$A$4,A2)>0,1,0),0)),Lookup!$B$2:$B$4)

Hit CTRL+SHIFT+ENTER to confirm

For more on the LOOKUP function, see expanded explanation on how LOOKUP and using 1/(parameters) work and why, in this recent solution:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27665631.html


Multiple criteria are doable as well, one only needs include it in the 1/(parameters) equation.

E.g., =LOOKUP(1,1/((firstparameter results in 1 or error)*(second parameter)*(etc))) and the result vector of the vlookup would be the result of having both parameters - in the solution, above, the Lookup!B2:B4 is the result vector (what LOOKUP would return on success).

See attached.

Dave
Excel---Map-text-to-category-r1.xlsx
Most Valuable Expert 2012
Top Expert 2012

Commented:
Also, for two criteria, I've built out your example, please see attached.

Dave
Excel---Map-text-to-category-r2.xlsx
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Dave,
In your sample workbooks, you don't need array formulas or IFERROR.

The following works for single criteria:
=LOOKUP(TRUE,FIND(Lookup!$A$2:$A$4,A2)>0,Lookup!$B$2:$B$4)

And for your workbook with multiple criteria:
=LOOKUP(1,(FIND(Lookup!$A$2:$A$4,A2)>0)*(FIND(Lookup!$B$2:$B$4,B2)>0),Lookup!$C$2:$C$4)

LOOKUP has the very nice property of ignoring error values. That's why you don't need the IFERROR and IF. And once you get rid of the IF and IFERROR, you don't need to array-enter.

Brad
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you want to avoid an error value in the case of no matches, then put the IFERROR outside the LOOKUP:
=IFERROR(LOOKUP(TRUE,FIND(Lookup!$A$2:$A$4,A2)>0,Lookup!$B$2:$B$4),"No match")

=IFERROR(LOOKUP(1,(FIND(Lookup!$A$2:$A$4,A2)>0)*(FIND(Lookup!$B$2:$B$4,B2)>0),Lookup!$C$2:$C$4),"No match")
Most Valuable Expert 2012
Top Expert 2012

Commented:
I generally put the error checking directly where I think it should be, though I suppose there's merit in both approaches.  I guess the key learning is that LOOKUP ignores errors and returns the index.  I didn't have to invert the equation for it to work - guess I've been on automatic with that since I first learned the function...

Thanks for that, Brad.  That's what's great about this forum.  You figure out how to help folks, and others jump in to help you ;)

Attached, please find updated demonstration.

Dave
lookupBranch-r3.xls
Most Valuable Expert 2012
Top Expert 2012

Commented:
So, I've been trying to help out on formulas more, so as with VBA, this stuff would roll off the fingers without too much brain squeak, and this is great.

Now, can you help me understand why I didn't need to invert the formulas and when they need to be because I thought I had that down.  I THOUGHT if the function I was using to do the lookup against had errors, then using the LOOKUP(1,1/(formula returns array some errors)) would be the correct approach, and I've seen barryhoudini do it time and time again.

Dave
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Dave,
Did you post a sample workbook from a different question?

When barryhoudini does the LOOKUP trick, 1/FIND returns either an error value or a number no bigger than 1. LOOKUP ignores the error value, and assumes that the lookup range has been sorted. It stops when it finds a value bigger than the first parameter. By using the reciprocal, you assure that the largest value will be 1.

With three parameters, as you have been using in this thread, LOOKUP returns a value corresponding to the last number that it does find in the list.

Bottom line: barryhoudini's method is a little more compact than looking for TRUE and doing the inequality like I did:

=IFERROR(LOOKUP(1,1/FIND(Lookup!$A$2:$A$4,A2),Lookup!$B$2:$B$4),"No match")

=IFERROR(LOOKUP(1,1/(FIND(Lookup!$A$2:$A$4,A2)*FIND(Lookup!$B$2:$B$4,B2)),Lookup!$C$2:$C$4),"No match")

Brad
Most Valuable Expert 2012
Top Expert 2012
Commented:
I guess I did.

@JCJG - Here's the sample solution with revised formulas!

Dave
Excel---Map-text-to-category-r3.xlsx
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ahh - so really the big issue with my original formula (other than array entry goof) was needing the IF statement.  The inversion converts to errors or creates values that aren't bigger than one already, so I didn't need the IF statement to force my numbers to be 1 on success.

That makes me feel much better.  However, understanding that I don't always have to invert is good, though its coming pretty naturally now, just need to remember lookup can handle without array entry.

Cheers,

Dave
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The IF function in your original formula forces it to be array-entered.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Well, that should be easy to remember.  Tks.

Dave

Author

Commented:
Thanks for both of your input!  This is great.

I realized the lookup in this example is case sensitive.  How do I change it so it is not?

In addition, I am now curious to know how to do the OR condition or even a combination of OR & AND conditions for 3 or more criteria.  Is it possible?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
For case insensitive matching, use the SEARCH function instead of FIND. Using Dave's most recent workbook, I suggest:
=IFERROR(LOOKUP(TRUE,SEARCH(Lookup!$A$2:$A$4,$A3)>0,Lookup!$C$2:$C$4),"")    single criteria

=IFERROR(LOOKUP(1,1/(SEARCH(Lookup!$A$2:$A$4,$A3)*SEARCH(Lookup!$B$2:$B$4,$B3)),Lookup!$C$2:$C$4),"")        two criteria

For three or more criteria, just multiply by additional SEARCH functions inside the parentheses.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
With this type of formula, you do AND by multiplying and OR by adding.

The devil is in the details, however. If you post a sample workbook with the exact problem you want solved, we'll suggest the best formula.

Author

Commented:
I apologize for not getting back sooner.  Using Dave's last file in this thread, I tried to change the logic from AND to OR by changing * to +.  It didn't work for me.  I was trying to assign category if either column A in the data tab matches the pattern in column A of the lookup tab OR column B in the data tab matches the pattern in column B of the lookup tab.  It returns blanks.

Please advise.  Thanks!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
=IFERROR(LOOKUP(1,(SEARCH(Lookup!$A$2:$A$4,$A3)>0)*1,Lookup!$C$2:$C$4),IFERROR(LOOKUP(1,(SEARCH(Lookup!$B$2:$B$4,$B3)>0)*1,Lookup!$C$2:$C$4),""))

Author

Commented:
Thanks!  You are not adding the criteria like you suggested earlier?
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Everything depends on the details of your workbook layout and the question being asked. In this case, I am using the SEARCH function--which returns an error value in the event of no match. The first working formula I thought of was the one posted.

But now that you demand one using criteria addition, how about:
=IFERROR(LOOKUP(1,1/(NOT(ISERR(SEARCH(Lookup!$A$2:$A$4,$A3)))+NOT(ISERR(SEARCH(Lookup!$B$2:$B$4,$B3)))),Lookup!$C$2:$C$4),"")

Author

Commented:
Thanks for all your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial