Lookup formula to find code from part of text

**mlun** · 11-06-2013, 03:33 AM

In sheet1 I have the following sample data (column B is what I am after):
clip.png

The code is what I want to find, and I have done it manually in this example. In sheet2 clip2.png I have a listing of strings and associated codes. The aim is to find the code automatically, for example in sheet1!B2 the formula should identify that the string "Abon" matches with code 87.

As there might be multiple occurrences it would be fine to stop at the first match.

Any ideas about how to achieve this?

**benishiryo** · 11-06-2013, 03:48 AM

hi mlun. are you still using Excel 2003? you should change your profile for the MS-Off Ver if it has changed. that will enable us to give you newer & more efficient formulas. also, it's better if you can upload a sample excel file next time so that we don't have to re-create your data to test it out.

if you don't mind having the last match instead of first match, then;
=LOOKUP(2^15,SEARCH(Sheet2!$A$2:$A$5,A2),Sheet2!$B$2:$B$5)

if you want the first match, maybe this array formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

change all my commas to semi-colons if your separators are supposed to be semi-colons

**mlun** · 11-06-2013, 04:50 AM

Thank you so much benishiryo!! That's really amazing, and problem solved!
I still use this very old Excel version

**arlu1201** · 11-06-2013, 08:35 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.