[Answered] MULTIPLE IF CONDITIONS FOR MULTIPLE PARTIAL TEXT MATCH

[iqubalind]

Dear Experts, Hi

I want suitable formula function for multiple partial text, the exact code results come to the related particulars, the same code number for one more particulars. so I want the right formula to clear my problems. Here I have attached my sample file. Please help.



Thanks and Regards,

 

[Aniruddah Alam]

Thank you Iqubalind for your post. It seems to me that you want to look for the HSN number corresponding to a PARTICULARS. You can easily do that using the VLOOKUP function. I have used the following formula to do that:

=VLOOKUP(E4,$B$3:$C$15,2,FALSE)



If I missed something, please don't hesitate to let me know.

Regards
Aniruddah
Team Exceldemy

 

[iqubalind]

Thank you Iqubalind for your post. It seems to me that you want to look for the HSN number corresponding to a PARTICULARS. You can easily do that using the VLOOKUP function. I have used the following formula to do that:

=VLOOKUP(E4,$B$3:$C$15,2,FALSE)

View attachment 872

If I missed something, please don't hesitate to let me know.

Regards
Aniruddah
Team Exceldemy

Thank you for your formula, please refer below formula function its working perfectly but its too long function.

=IF(OR(ISNUMBER(SEARCH("AIR",E4)),ISNUMBER(SEARCH("TRAIN",E4)),ISNUMBER(SEARCH("BUS",E4))),"998551","")&IF(OR(ISNUMBER(SEARCH("TAXI",E4))),"996412","")&IF(OR(ISNUMBER(SEARCH("ATTESTATION",E4))),"998212","")&IF(OR(ISNUMBER(SEARCH("OTHER ONLINE SERVICES",E4))),"998319","")&IF(OR(ISNUMBER(SEARCH("TOUR",E4)),ISNUMBER(SEARCH("HOTEL",E4)),ISNUMBER(SEARCH("CRUISE",E4))),"998552","")&IF(OR(ISNUMBER(SEARCH("VISA",E4)),ISNUMBER(SEARCH("PASSPORT",E4)),ISNUMBER(SEARCH("EMIGRATION",E4))),"998559","").

But I want simple formula function for same results its possible please help.

Thanks & Regards,

 

[shamimarita]

Thank you for your formula, please refer below formula function its working perfectly but its too long function.

=IF(OR(ISNUMBER(SEARCH("AIR",E4)),ISNUMBER(SEARCH("TRAIN",E4)),ISNUMBER(SEARCH("BUS",E4))),"998551","")&IF(OR(ISNUMBER(SEARCH("TAXI",E4))),"996412","")&IF(OR(ISNUMBER(SEARCH("ATTESTATION",E4))),"998212","")&IF(OR(ISNUMBER(SEARCH("OTHER ONLINE SERVICES",E4))),"998319","")&IF(OR(ISNUMBER(SEARCH("TOUR",E4)),ISNUMBER(SEARCH("HOTEL",E4)),ISNUMBER(SEARCH("CRUISE",E4))),"998552","")&IF(OR(ISNUMBER(SEARCH("VISA",E4)),ISNUMBER(SEARCH("PASSPORT",E4)),ISNUMBER(SEARCH("EMIGRATION",E4))),"998559","").

But I want simple formula function for same results its possible please help.

Thanks & Regards,

Can you please tell us which version of Excel you are using?
If you use Excel 2010 and later versions you can use the VLOOKUP function mentioned above.

Here I am attaching a drop-down list with the previous formula so that if you enter any value in E4 cell then you will get the respective HSN Number.
Video : Formula

 

[iqubalind]

Can you please tell us which version of Excel you are using?
If you use Excel 2010 and later versions you can use the VLOOKUP function mentioned above.

Here I am attaching a drop-down list with the previous formula so that if you enter any value in E4 cell then you will get the respective HSN Number.
Video : Formula


View attachment 925

I am using excel 2010 version. Actually I want the right simple formula for partial text only is here is same code for different services that's the reason I want formula find results to each same code for different services. So I want simple best formula function instead of my currently using formula. Please resolve my problem earlier as possible. Find attached file here.

 

[MD Tanvir Rahman]

I am using excel 2010 version. Actually I want the right simple formula for partial text only is here is same code for different services that's the reason I want formula find results to each same code for different services. So I want simple best formula function instead of my currently using formula. Please resolve my problem earlier as possible. Find attached file here.

Dear iqubalind, you have my heartfelt gratitude. I have developed two formulas for direct match and partial match. Partial match is also applicable for direct match. Microsoft introduced VLOOKUP, INDEX, and MATCH functions in the Excel 2007 version.

Formula applied in the G3 cell, =VLOOKUP(E3,B3:C14,2,FALSE)
Frmula applied in the G4 cell, =INDEX(C3:C14, MATCH("*" & E4 & "*", B3:B14, 0))

Example-1:


Example 2:


Also, I have attached the Excel file. Please check it out. I hope these approaches will solve your issues. For any further shortcomings, please let me know.
Thank you so much. Please stay tuned with ExcelDemy.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

 

[iqubalind]

Dear iqubalind, you have my heartfelt gratitude. I have developed two formulas for direct match and partial match. Partial match is also applicable for direct match. Microsoft introduced VLOOKUP, INDEX, and MATCH functions in the Excel 2007 version.

Formula applied in the G3 cell, =VLOOKUP(E3,B3:C14,2,FALSE)
Frmula applied in the G4 cell, =INDEX(C3:C14, MATCH("*" & E4 & "*", B3:B14, 0))

Example-1:
View attachment 933

Example 2:
View attachment 934

Also, I have attached the Excel file. Please check it out. I hope these approaches will solve your issues. For any further shortcomings, please let me know.
Thank you so much. Please stay tuned with ExcelDemy.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

The formula not working when I adding same additional words in the same cells. The suitable formula should work if I going to add same additional text like "Air Service India to Singapore" "MAA to SIN Air service" now the formula should be found the partial text of "Air", "Bus", "Train" etc...

Please help.

Thanks and Regards,

 

[MD Tanvir Rahman]

The formula not working when I adding same additional words in the same cells. The suitable formula should work if I going to add same additional text like "Air Service India to Singapore" "MAA to SIN Air service" now the formula should be found the partial text of "Air", "Bus", "Train" etc...

Please help.

Thanks and Regards,

Dear @iqubalind, Are you looking for a dynamic formula? If your answer is Yes! please jump to the newly developed formula.

=INDEX($C:$C, MATCH("*" & F8 & "*", $B:$B, 0))

I have attached a GIF. Please have a look carefully. You just require to change the column number (i.e. B,C,D) according to your dataset from the formula.



I hope this approach will help you to solve the issue. Besides, you can send me the Excel file where you want to apply the formula, thus I will be able to help efficiently.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

 

[iqubalind]

Dear @iqubalind, Are you looking for a dynamic formula? If your answer is Yes! please jump to the newly developed formula.

=INDEX($C:$C, MATCH("*" & F8 & "*", $B:$B, 0))

I have attached a GIF. Please have a look carefully. You just require to change the column number (i.e. B,C,D) according to your dataset from the formula.

View attachment 943

I hope this approach will help you to solve the issue. Besides, you can send me the Excel file where you want to apply the formula, thus I will be able to help efficiently.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

Dear Mr. MD Tanvir Rahman, I don't want add more services, Actually I want the right formula to find the related multiple services for same code results. The formula could be refer correctly the partial respective service text only even if I going to adding some other texts in the same cells. But your formula not working correctly while I adding some other text in the same cell. So please consider my actual requirement carefully and resolve my problem earlier as possible.

Thanks and Regards,

 

[MD Tanvir Rahman]

Dear Mr. MD Tanvir Rahman, I don't want add more services, Actually I want the right formula to find the related multiple services for same code results. The formula could be refer correctly the partial respective service text only even if I going to adding some other texts in the same cells. But your formula not working correctly while I adding some other text in the same cell. So please consider my actual requirement carefully and resolve my problem earlier as possible.

Thanks and Regards,

Dear @iqubalind, I have acknowledged that you wanted to use the shorter version of the following formula for partial match you mentioned above,

=IF(OR(ISNUMBER(SEARCH("AIR",E4)),ISNUMBER(SEARCH("TRAIN",E4)),ISNUMBER(SEARCH("BUS",E4))),"998551","")&IF(OR(ISNUMBER(SEARCH("TAXI",E4))),"996412","")&IF(OR(ISNUMBER(SEARCH("ATTESTATION",E4))),"998212","")&IF(OR(ISNUMBER(SEARCH("OTHER ONLINE SERVICES",E4))),"998319","")&IF(OR(ISNUMBER(SEARCH("TOUR",E4)),ISNUMBER(SEARCH("HOTEL",E4)),ISNUMBER(SEARCH("CRUISE",E4))),"998552","")&IF(OR(ISNUMBER(SEARCH("VISA",E4)),ISNUMBER(SEARCH("PASSPORT",E4)),ISNUMBER(SEARCH("EMIGRATION",E4))),"998559","")

However, you can perform the same operation using the VLOOKUP function for partial matches.

Step 1: Use the following formula in the F3 cell:

=VLOOKUP("*"& $E3 &"*", $B$3:$C$14, 2, FALSE)




Step 2: Use the Fill Handle tool to auto-fill the rest.



I hope this approach will help you to solve the issue. Thank you so much. Please let me know your further findings.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

 

[iqubalind]

Dear @iqubalind, I have acknowledged that you wanted to use the shorter version of the following formula for partial match you mentioned above,

=IF(OR(ISNUMBER(SEARCH("AIR",E4)),ISNUMBER(SEARCH("TRAIN",E4)),ISNUMBER(SEARCH("BUS",E4))),"998551","")&IF(OR(ISNUMBER(SEARCH("TAXI",E4))),"996412","")&IF(OR(ISNUMBER(SEARCH("ATTESTATION",E4))),"998212","")&IF(OR(ISNUMBER(SEARCH("OTHER ONLINE SERVICES",E4))),"998319","")&IF(OR(ISNUMBER(SEARCH("TOUR",E4)),ISNUMBER(SEARCH("HOTEL",E4)),ISNUMBER(SEARCH("CRUISE",E4))),"998552","")&IF(OR(ISNUMBER(SEARCH("VISA",E4)),ISNUMBER(SEARCH("PASSPORT",E4)),ISNUMBER(SEARCH("EMIGRATION",E4))),"998559","")

However, you can perform the same operation using the VLOOKUP function for partial matches.

Step 1: Use the following formula in the F3 cell:

=VLOOKUP("*"& $E3 &"*", $B$3:$C$14, 2, FALSE)

View attachment 958


Step 2: Use the Fill Handle tool to auto-fill the rest.

View attachment 957

I hope this approach will help you to solve the issue. Thank you so much. Please let me know your further findings.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

Dear Mr. MD Tanvir Rahman, Again the formula is not working please find the file attached here and resolve my problem please...

 

[MD Tanvir Rahman]

Dear Mr. MD Tanvir Rahman, Again the formula is not working please find the file attached here and resolve my problem please...

Dear @iqubalind, I hope you are in sound health. Yes, I got your issues. You are likely to match the text string and return the HSN number.

Use the following formula,
=IF(ISTEXT($E3),INDEX($C$3:$C$14, MATCH(TRUE, ISNUMBER(SEARCH(LEFT($E3, FIND(" ", $E3 & " ") - 1), $B$3:$B$14)), 0)),"")

Please have a look closely I have developed the formula and it works perfectly. Even all the functions I have used, Excel 2007 supported.


Note:
I have matched, the 1st word only to avoid complexity. Likewise, I checked "Tour" from the "Tour Service to Singapore" with the 1st word of the B3:B14 range. Eventually, it finds "Tour" in the B9 cell. Therefore, it picks up HSN number 998552 in the F3 cell.



Thank you so much. Please let me know your further findings. Besides, check out the Excel file.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

 

[iqubalind]

Dear @iqubalind, I hope you are in sound health. Yes, I got your issues. You are likely to match the text string and return the HSN number.

Use the following formula,
=IF(ISTEXT($E3),INDEX($C$3:$C$14, MATCH(TRUE, ISNUMBER(SEARCH(LEFT($E3, FIND(" ", $E3 & " ") - 1), $B$3:$B$14)), 0)),"")

Please have a look closely I have developed the formula and it works perfectly. Even all the functions I have used, Excel 2007 supported.
View attachment 969

Note:
I have matched, the 1st word only to avoid complexity. Likewise, I checked "Tour" from the "Tour Service to Singapore" with the 1st word of the B3:B14 range. Eventually, it finds "Tour" in the B9 cell. Therefore, it picks up HSN number 998552 in the F3 cell.

View attachment 968

Thank you so much. Please let me know your further findings. Besides, check out the Excel file.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy

Dear Mr. MD Tanvir Rahman, the formula amazing but any other suitable formula can find the particular partial text anywhere in the same cells instead of the RIGHT, LEFT formulas, its possible...?



Thanks and Regards,

 

[MD Tanvir Rahman]

Dear Mr. MD Tanvir Rahman, the formula amazing but any other suitable formula can find the particular partial text anywhere in the same cells instead of the RIGHT, LEFT formulas, its possible...?



Thanks and Regards,

Dear @iqubalind, take my heartfelt gratitude. The RIGHT, LEFT, and MID are suitable functions since we are set to match a word (i.e. Train, Bus, Emigration, and so on) from the beginning of the text. But yes, without these functions, we can still develop formulas but those are not as simple as this one. Perhaps, these other developed formulas create complexity. I have tried some of them but this one is the simplest for partial match in my opinion.

>>Formula:
=IF(ISTEXT($E3),INDEX($C$3:$C$14, MATCH(TRUE, ISNUMBER(SEARCH(LEFT($E3, FIND(" ", $E3 & " ") - 1), $B$3:$B$14)), 0)),"")

Note: We could use the SEARCH function single-handedly. But the problem is, the SEARCH function finds "Tour Service" from "Tour Service to Singapore". If you write "Tour Consultant" instead of "Tour Service Consultant" it stops working and returns a #N/A error. Please check out the image below for clarification:



I hope I have made it clear, why I applied the LEFT and FIND functions inside the SEARCH function. Thank you have a good day. Please hit (Thumbs up) if the solution helps you in any way. Also, don't forget to remember us for any further shortcomings. We, team ExcelDemy ready to serve you with our knowledge, skills, expertise, and resources.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
ExcelDemy

 

[iqubalind]

Dear @iqubalind, take my heartfelt gratitude. The RIGHT, LEFT, and MID are suitable functions since we are set to match a word (i.e. Train, Bus, Emigration, and so on) from the beginning of the text. But yes, without these functions, we can still develop formulas but those are not as simple as this one. Perhaps, these other developed formulas create complexity. I have tried some of them but this one is the simplest for partial match in my opinion.

>>Formula:
=IF(ISTEXT($E3),INDEX($C$3:$C$14, MATCH(TRUE, ISNUMBER(SEARCH(LEFT($E3, FIND(" ", $E3 & " ") - 1), $B$3:$B$14)), 0)),"")

Note: We could use the SEARCH function single-handedly. But the problem is, the SEARCH function finds "Tour Service" from "Tour Service to Singapore". If you write "Tour Consultant" instead of "Tour Service Consultant" it stops working and returns a #N/A error. Please check out the image below for clarification:

View attachment 974

I hope I have made it clear, why I applied the LEFT and FIND functions inside the SEARCH function. Thank you have a good day. Please hit (Thumbs up) if the solution helps you in any way. Also, don't forget to remember us for any further shortcomings. We, team ExcelDemy ready to serve you with our knowledge, skills, expertise, and resources.

Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
ExcelDemy

Dear Mr. MD Tanvir Rahman, Sorry the formulas not working properly and I did not get the suitable results for any one of code correctly. I am also try many time but I could not found the problems also.

Note: My excel version is 2016


Thanks and Regards,