Cell without exact match

[joeyjee]

Dear All,

I am been looking around for the correct formula to use. Even tried using Vlook with not exact match but the result turn out to be incorrect.

I have a set of accounts, example

Account Name Return Result
ABC Singapore Pte Ltd Cargo
ABC SG #NA
XYZ Pte. #NA
XYZ (S) Pte Ltd Shipping
XYZ SG Pte. #NA

I would like accounts with all ABC accounts to return "Cargo", while XYZ accounts to return "Shipping", but the results I had obtained so far after vlook is the above.

Anyone can assist, please?



Thanks,
Joey~

 

[outlawdevil]

can you run it in two columns for check?
IE data in Column A
Column B=IF(COUNTIF(A1,"*ABC*"),"Cargo",0)
Column C=IF(COUNTIF(A1,"*XYZ*"),"Shipping",0)

you will get something like this then just do a filter and copy paste over to one column.

 

[joeyjee]

Hi outlawdevil,

Thanks for your advice! I guess I might not have explained correctly.

Account Name Sheet Actual Full Name in other Sheet Return Result
ABC Singapore Pte Ltd ABC Singapore Pte Ltd Cargo
ABC SG ABC Singapore Pte Ltd #NA
XYZ Pte. XYZ (S) Pte Ltd #NA
XYZ (S) Pte Ltd XYZ (S) Pte Ltd Shipping
XYZ SG Pte. XYZ (S) Pte Ltd #NA


If in my Account Name Sheet, the naming convention is not complete one, we should follow the full name in the "Actual Full Name in other Sheet" to return the correct Result "Cargo" or "Shipping", and the list of incomplete Account name in the Account Name sheet is super long, what is the best formula to use?

thanks!!