• 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.

Need formula help partial lookup and other

V

vinu

Member
I need formula for 2 of my scenarios: I have 2 list of data; List 1 contains 10 customer name; list 2 contains 500 customer name.

List 2 contains customer name with little change(like prefix or suffix of something eg: list 1 vodafone, list 2 vodafone ltd)

Scenario 1: I need to find out in list 2 by looking up at list 1, customer available or not. If list 1 customer name partially matches with list 2 it should write available or else not available.


Scenario 2: Need to list of the customer names which is duplicated by prefix or suffix. Eg: List 1 has 10 companies name, but those would have appeared in list 2 by little changes for eg 20 names. Eg list 1 has Vodafone; list 2 has Vodafone, vodfone india, Vodafone ltd etc., all thsese I need to list out.


Thanks
 
For each value in List 1 you can determine how many are represented in List 2 by using countif and a wild card for the List 1 value:

COUNTIF(E$2:E$11,"*"&A2&"*") or Countif(range for list 2,"*"value from list 1"*")


Availability can now be determined based on the result: If the result for the item in list 1 = 0, then "Not Available", otherwise "Available"

=IF(C2=0,"Not Available","Available")
 
Thanks for your answer. Where as I need formula to enter infront of List2 not in List1.Eg:countif(range1,range2).

And in 2nd scenario I need to list out the reapeted with little change customer names.


Thnx.
 
You must log in or register to reply here.
Back
Top