• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding variations of an ID with numbers and text

L

lgf999

New Member
I am trying to lookup all variations of a value within a range of cells. I have a "core" number that I am looking up and I want to find every item in the other list that includes that "core" number.


For example, my "core" number in my raw data is 5402-03. In the list that I am looking in, the variations of that number include 5402BAM-03, 5402BR-03, 5402CT-03.


Is there a formula I can use to look up my "core" number and return to me the list of all variations?
 
Hi Igf999,


You can use conditional format to highlight all the cells that match your criteria


as follows =Left(Cell,4)=Left(Core,4)


or you can use a helper column with the same formula =Left(Cell,4)=Left(Core,4), this will return true for all cells that match the criteria.


cheers


kanti
 
Hi Igf999,


Or may be this assumming data in column A


=IF(LOOKUP(99^99,1*(0&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)+1)))))&"-"&RIGHT(A1,2)="5402-03",TRUE,FALSE)
 
You must log in or register to reply here.
Back
Top