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?
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:
=IF(ISNA(LOOKUP(2^15,SEARCH(Sheet2!$A$2:$A$5,A2))),NA(),INDEX(Sheet2!$B$2:$B$5,MIN(IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$5,A2)),ROW(Sheet2!$A$2:$A$5)-ROW(Sheet2!$A$2)+1))))
...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
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thank you so much benishiryo!! That's really amazing, and problem solved!
I still use this very old Excel version
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.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks