Results 1 to 2 of 2

Thread: Compare text within entire rand of column and return desired value

  1. #1
    Neophyte Imran0709's Avatar
    Join Date
    Aug 2019
    Excel Version

    Compare text within entire range of column and return desired value

    Register for a FREE account, and/
    or Log in to avoid these ads!

    So here’s my problem.

    Column A contains list of cities say

    A1 has pune
    A2 has mumbai
    A3 delhi
    A4 delhi chandigarh


    1. I want to say find “delhi” anywhere through A1 to A4.
    2. Whichever row number it finds it in, it’s corresponding row of the next column should return “del”

    Eg. Here it will find “delhi” in A3 so in B3 it should return “del”

    This could be achieved with if(isnumber(search))) function. However, my problem statement is a little complex.

    3. Along with finding “delhi” I also want to find “delhi chandigarh” and return “dch” in the corresponding row of the next column wherever it finds “delhi chandigarh”

    Problem: Since it take up the the 1st occurrence. Here, once it finds “delhi” in A3 and writes “del” in B3 and moves to A4 it again checks for “delhi” first and finds it in A4 as well and writes “del” in B4. The next condition of checking “delhi chandigarh” doesn't happen only and in both B3 as well as B4 it shows up as “del”.

    Ideally, I would want

    A3 “delhi” so B3 “del” and A4 “delhi chandigarh” so b4 “dch”

    Please help at the earliest.
    Last edited by Imran0709; 2019-08-02 at 08:53 PM.

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Brussels Belgium
    Excel Version
    2010 on Xubuntu
    Please post a sample sheet.

    Also take into account that this a a free forum where members help your for FREE on their spare time.
    You cannot expect them to be waiting in front of their keyboard for members looking for urgent answers.
    You can always contact pay sites for urgent matters
    Thank you Ken for this secure forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts