Hi, in my system, we have a Short Name for supplier. Example ABC Pte Ltd might become ABC Pt etc. There is no specific text length for this Short Name.
I attached a sample of the short name and the actual name. Is there a way to use formula to extract the actual name base on short name?
I've used your list in columns A & B as your source. Complete this list with all the suppliers full name (B) and their short codes (A)
Put this in Column D;
Formula:
Please Login or Register to view this content.
And copy down. In the formula replace A2 with the location of the cell you are wanting to lookup the value of.
This is meant to be an example of how to do what you want.
I think you misunderstanding my meaning. I only have Column A and D. Column D is a long long list, i need a formula to read the Column A and search for match value in Column D.
Maybe in B2:
Or, neater for first n digits:
Please Login or Register to view this content.
Please Login or Register to view this content.
Quang PT
Hi Bebo21999
Your formula work. But what does the 2 mean? And what does 1/ mean? And also, i remove the "*" and the result is the same. How come?
1/- Assuming:Originally Posted by jp16
"ABC" vs list "XXX ABC YYY"(1), "ABC XXX"(2)
"ABC" or "*ABC*" refer to both (1) and (2)
"ABC*" refer to (2) only
2/-
=LOOKUP(2,1/SEARCH(A2&"*",$D$2:$D$8),$D$2:$D$8)
1/SEARCH(...) returns list of 1 & #DIV/0
LOOKUP(n,1/...,D2:D8) with any n>1 with refer to value 1 in list, and returns text in D2:D8
ARRAY formula in B2 then drag down.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
or try
Formula:Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks