Formula required to pick up if another cell returns a certain value

Soares79

New Member
Joined
Sep 4, 2014
Messages
22
Hi,

I need a formula to return a certain value if another cell returns a certain value. I can see heaps of formulas online, but this seems to be only if the cell has text in it. My cell has a formula picking up results from another sheet.

So I need a formal in Column D to say "Open" or "Closed" is the formula in G returns a value that contains the letters "CONTIN". The returned value on Column G will show a result of "AEC ANZ-CONTIN-002528", but this number will keep changing, only the element "CONTIN" is consitent.

Hope this makes sense, but all thng i have tried seems to look at the formula in Column G, not the returned value, so I cant make it work.

Thanks,

Rod.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
So, if you want the formula in D1 to say "Open" if the word "CONTIN" appears in cell G1, and you want it to say "Closed" if it does not, put this formula in cell D1:
Excel Formula:
=IF(ISNUMBER(SEARCH("CONTIN",G1)),"Open","Closed")
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,992
Office Version
  1. 365
Platform
  1. Windows
Unless you use the 'FORMULATEXT' function any formula will look at the result that you seen in the cell, not the underlying formula.
Certain formatted values such as dates may cause some confusion if you were trying to search for a month by name, but there should be no such issues with what you have.
 

Soares79

New Member
Joined
Sep 4, 2014
Messages
22
Ha! brilliant, thanks so much guys. I had a very similar formula written but it wouldn't work - I had the g1 cell reference and the "CONTIN" the wrong way around.

Just to add a little more complexity, can I add some more search field? Over and above "CONTIN", can I also see if it says "received" or 'RTRFI". All three words would still result in Closed appearing.
 

Soares79

New Member
Joined
Sep 4, 2014
Messages
22
Ha! brilliant, thanks so much guys. I had a very similar formula written but it wouldn't work - I had the g1 cell reference and the "CONTIN" the wrong way around.

Just to add a little more complexity, can I add some more search field? Over and above "CONTIN", can I also see if it says "received" or 'RTRFI". All three words would still result in Closed appearing.
Dont worry, figured it out. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,393
Messages
5,601,403
Members
414,449
Latest member
Pashtun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top