Imran0709
New member
- Joined
- Aug 2, 2019
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2013
Compare text within entire range of column and return desired value
So here’s my problem.
Column A contains list of cities say
A1 has pune
A2 has mumbai
A3 delhi
A4 delhi chandigarh
Now,
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.
So here’s my problem.
Column A contains list of cities say
A1 has pune
A2 has mumbai
A3 delhi
A4 delhi chandigarh
Now,
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:
