How to return value in another cell if a cell contains certain text in Excel?
Supposing you have a range of cells, if cell A1 contains a certain text such as “Yes”, you need another cell C1 to return a specific value “approve”. And if cell A1 contains other text, cell C1 returns nothing. This article shows you method to achieve it.
Recommended Productivity Tools for Excel/Office
As the example we mentioned above, you can apply the following formula to deal with this problem.
1. Select cell C1 you need to populate value based on text in cell A1, then enter formula =IF(ISNUMBER(SEARCH("Yes",A1)),"approve","") into the formula bar, and then press the Enter key.
Note: In the formula, “Yes”, A1, and “approve” indicate that if cell A1 contains text “Yes”, the selected cell will be populated with text “approve”. You can change them based on your needs.
Then you can see when cell A1 contains text “Yes”, the text “approve” will be populated into the selected cell. But if cell A1 contains other text, the selected cell will be populated with nothing. See screenshot:
- How to auto populate other cells when selecting values in Excel drop down list?
- How to autocomplete when typing in Excel drop down list?
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 days agoHi, I'm trying to use =IF(ISNUMBER(SEARCH("Name",B20)),"EmployeeNumber","") where I have an input field of Between Cell B20-B75 and I want that If I put a specific person's name in any cell from B20-B75 that their employee number will auto-populate then in the corresponding D column, from D20-D75. So say I put Peter Smith in B20 I want Peter Smith's employee number 700000001to then appear in cell D20, but then should his name appear again anywhere within B20-B75 the same should occur in the corresponding D cell. I hope that's clear enough. Please help!
To post as a guest, your comment is unpublished.· 4 months agowhat if I want to add same formula but multiple years? I am trying to say if it contains 2012 then copy value 2012... if text contains 2013 then copy value 2012
To post as a guest, your comment is unpublished.· 6 months agoHI, if I put JANUARY in cell A1 I would like cell B1 to automatically show "JULY" and so on for the rest of the year with 6 months in between. I don't mind writing a IF JAN then JULY - FEB then AUG formula for each of the months, just can't seem to work out which formula to use and what to write.
Hope this makes sense. Thank you
To post as a guest, your comment is unpublished.· 6 months agoHi,
I am trying to get the below formula to work.
It seems to work fine if A1 is populated with "Name", but just states False when A1 is changed to "Code".
How can I get it to Concatenate if either one of Name or Code is in A1?
To post as a guest, your comment is unpublished.· 6 months agoHi, I'm curious if this method can be used to auto-fill a series of cells depending on their value by referencing an index list. For example, I've got a list of names numbered 1-10, and have a grid (Grid 1) that has various numbers from that 1-10 in them. I'd like to find a way for the spreadsheet to fill in Grid 2 with the name associated with the number in Grid 1. For example, if D3 (Grid 1) is "2" and the name associated with "2" is "Jerry" then D12 should autofill with "Jerry," but if D3 is changed to "9" then D12 should automatically change to "Goldfish"
To post as a guest, your comment is unpublished.· 6 months agonevermind, I figured it out, just nested a TON of =IF statements: