We help IT Professionals succeed at work.

excel formula with conditions

ITSupportLCBT
on
224 Views
Last Modified: 2012-05-29
I'm working on an excel spread sheet with three columns. Both column A and column B contain some data. Column C is empty. I need to populate column C based on the content of columns A and B. Specifically:

If  the string 'ABC' exits s anywhere in  the contents of cell A1, then I need the content of cell C1 to be whatever the content of B1 is but pre-fixed with 'UK-'.  If 'ABC' does not exist in A1, then C1 should remain blank.

For example if the content of A1 is 'test-ABC' and the content of  B1 is 'cat' then C1 should become 'UK-cat'.


What is the formula that will allow me to do this please?
Comment
Watch Question

Analyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Your formula which you can copy without changing it into all cells in column C is:

=IF(ISNA(VLOOKUP("*"&$G$1&"*";A1:A1;1;));"";"UK-"&B1)

Additionally I have attached a spreadsheet showing it....
Formula.xls
ha ha ... I was 27 seconds too late ... shouldnt have attached the spreadsheet .... :-))))
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.