Note: The other languages of the website are Google-translated. Back to English

How to return value in another cell if a cell contains certain text in Excel?

As the example shown below, when cell E6 contains the value “Yes”, cell F6 will be automatically populated with the value “approve”. If you change “Yes” to “No” or “Neutrality” in E6, the value in F6 will be changed to “Deny” or “Reconsider” immediately. How can you do to achieve it? This article collects some useful methods to help you easily solve it.


Return value in another cell if a cell contains certain text with formula

For returning value in another cell if a cell contains only a specific text, please try the following formula. For example, if B5 contains “Yes”, then return “Approve” in D5, otherwise, return “No qualify”. Please do as follows.

Select D5 and copy below formula into it and press the Enter key. See screenshot:

Formula: Return value in another cell if a cell contains certain text

=IF(ISNUMBER(SEARCH("Yes",D5)),"Approve","No qualify")

Notes:

1. In the formula, “Yes”, D5, “approve” and “No qualify” indicate that if cell B5 contains text “Yes”, the specified cell will be populated with text “approve”, otherwise, it will be filled with “No qualify”. You can change them based on your needs.

2. For returning value from another cells (such as K8 and K9) based on a specified cell value, please use this formula:

=IF(ISNUMBER(SEARCH("Yes",D5)),K8,K9)

Easily select entire rows or entire rows in the selection based on cell value in a certain column:

The Select Specific Cells utility of Kutools for Excel can help you quickly select entire rows or entire rows in the selection based on specific cell value in a certain column in Excel.  Download the full feature 60-day free trail of Kutools for Excel now!


Return values in another cell if a cell contains different texts with formula

This section is going to show you the formula for returning values in another cell if a cell contains different text in Excel.

1. You need to create a table with the specific values and return values that located separately in two columns. See screenshot:

2. Select a blank cell for returning the value, type the below formula into it and press the Enter key to get the result. See screenshot:

Formula: Return values in another cell if a cell contains different texts

=VLOOKUP(E6,B5:C7,2,FALSE)

Notes:

In the formula, E6 is the cell contains the specific value you will return value based on, B5:C7 is the column range containing the specific values and the return values, the 2 number means that the return values locating on the second column in the table range.

From now on, when changing the value in E6 to a specific one, its corresponding value will be returned in F6 immediately.


Easily return values in another cell if a cell contains different texts

Actually, you can solve the above problem by an easier way. The Look for a value in list utility of Kutools for Excel can help you to achieve it with only several clicks without remembering formula.

1. The same as the above method, you also need to create a table with the specific values and return values that locate separately in two columns.

2. Select a blank cell to output the result (here I select F6), and then click Kutools > Formula Helper > Formula Helper. See screenshot:

3. In the Formula Helper dialog box, please configure as follows:

  • 3.1 In the Choose a formula box, find and select Look for a value in list;
    Tips: You can check the Filter box, enter certain word into the text box to filter the formula quickly.
  • 3.2 In the Table_array box, select the table without headers you have created in step 1;
  • 3.2 In the Lookup_value box, select the cell contains the specific value you will return value based on;
  • 3.3 In the Column box, specify the column you will return the matched value from. Or you can enter the column number into the textbox directly as you need.
  • 3.4 Click the OK button. See screenshot:

From now on, when changing the value in E6 to a specific one, its corresponding value will be returned in F6 immediately. See result as below:

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


Kutools for Excel - Helps You Always Finish Work Ahead of Time, Have More Time to Enjoy Life
Do you often find yourself playing catch-up with work, lack of time to spend for yourself and family?  Kutools for Excel can help you to deal with 80% Excel puzzles and improve 80% work efficiency, give you more time to take care of family and enjoy life.
300 advanced tools for 1500 work scenarios, make your job so much easier than ever.
No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
Complicated and repeated operations can be done a one-time processing in seconds.
Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
Become an Excel expert in 3 minutes, help you quickly get recognized and a pay raise promotion.
110,000 highly effective people and 300+ world-renowned companies' choice.
Make your $39.0 worth more than $4000.0 training of others.
Full feature free trial 30-day. 60-Day Money Back Guarantee without reason.

Comments (95)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, This is great, however is it possible to do this with multiple different conditions..for example: if the cell contains 15105 then it will produce 10 and if the cell contains pdh152 then it will produce 11 and so on? Regards, Brandon.
This comment was minimized by the moderator on the site
I'm wondering the same question, can't get it to work. =IF(ISNUMBER(SEARCH("A,C",B:B)),"Alameda,Chicago","") something like this
This comment was minimized by the moderator on the site
Great article, does exactly what I wanted... almost! Same question as above, how to get this function to work with multiple conditions, e.g. If A1=yes, then B1=approve OR IF A1=no, then B1=deny
This comment was minimized by the moderator on the site
Has no one found a solution for this? It would be very helpful.
This comment was minimized by the moderator on the site
@Gary,
What are you trying to accomplish?


@John,
=IF(A1="YES","APPROVED",IF(A1="NO","DENY",""))
This comment was minimized by the moderator on the site
If you are still having trouble then try this example: =IF(ISNUMBER(SEARCH("Yes",A1)),"approve",IF(ISNUMBER(SEARCH("NO",A1)),"unapproved","")
This comment was minimized by the moderator on the site
I just want the same thing. But your example is bit complicated. It is not easy to type this much say if I wanted to do the same with ten numbers.plz provide code for that.
This comment was minimized by the moderator on the site
I did this for 4 values, i just copy and pasted from 'IF' and changed what I wanted the cell to read.
This comment was minimized by the moderator on the site
Not sure what pdh152 means, but hope this helps. The first randbetween will be producing 10 digits, while the second will produce 11.


=IF(OR(A1:A5)="15105",RANDBETWEEN(1,1000000000),RANDBETWEEN(1,10000000000))
This comment was minimized by the moderator on the site
I am trying to create a spreadsheet for automatic password creation. I only want a cell to be populated if one of the previous cells has a value in it.the formula i want to use is

=CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(97;122))&CHAR(RANDBETWEEN(65;90))&RANDBETWEEN(1000;9999)&CHAR(RANDBETWEEN(42;43))


so if one of the previous fields has a value in it (doesnt matter which one) then populate another cell with the above formula

A B C D E F
1 First Name Last Name Email Address Phone Number Username Password

2 Joe Blow jb@jb.com 123456 blowjo fh7#rfd


so say if A2 is filled then the formula in F2 is returned, if there is no value in A2 the field is left blank
This comment was minimized by the moderator on the site
Sorry, hope I understood your query..


=IF(OR(A2<>"",B2<>"",C2<>"",D2<>"",E2<>"",F2<>""),RANDBETWEEN(1,10000000000),"")
This comment was minimized by the moderator on the site
Please help. If a cell has a word in it show a number from another cell
This comment was minimized by the moderator on the site
Dear Dave,

Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
This comment was minimized by the moderator on the site
hai anybody there to help
This comment was minimized by the moderator on the site
hai, anybody thr to help me in excel?
This comment was minimized by the moderator on the site
Hi, I want a cell to equal the sum amount of another cell if the first cell equals a certain text. So, if A1 equals “yes” then A3 equals A2. A2 has a previously calculated sum in it of say £100
This comment was minimized by the moderator on the site
Dear Luke,
Please apply this formula =IF(A1="yes",A2,"") in A3, and finally change the cell format of A3 to the proper currency format as you need.
This comment was minimized by the moderator on the site
Need a 'How To'......If a certain cell contains a specific set of words...which includes parenthesis, ex. C&I(MVA)
I would like the column to the right to add 12. Haven't been able to figure it out.
This comment was minimized by the moderator on the site
Don't know where my post went.....
trying to create a formula when the column has the words: C & I (MVA), I want the cell to the right to add 12.
Can't seem to figure it correctly.
This comment was minimized by the moderator on the site
Need a 'How To'......If a certain cell contains a specific set of words...which includes parenthesis, ex. C&I(MVA)
I would like the column to the right to add 12. Haven't been able to figure it out.
This comment was minimized by the moderator on the site
881329297 89-267 SINE, TODD A 1989 C & I (MVA) FINE 1989 2001
A B C D E F G H

I would like column H to add 12 from column G, only if column E contains C & I (MVA). The parenthesis pose a problem as does the ampersand.
This comment was minimized by the moderator on the site
Dear Kim,
This formula can help you: =IF(E1:E12="C & I (MVA)", G1+12, " ")
This comment was minimized by the moderator on the site
Tried this and it keeps coming up 'false'
=IF(E1="C & I","C & I (MVA)",G1+12=H1)
This comment was minimized by the moderator on the site
i need text in once cell to populate another cell. i.e. if cell c2:c25 is "asian" then cell a2:a25 = DBE if not leave blank? can anyone help me with this formula
This comment was minimized by the moderator on the site
Good Day,
Sorry I didn't get your point. Would be nice if you could provide screenshot of what you are trying to do.
This comment was minimized by the moderator on the site
Thanks for the great advice as always! I would like to know if there is a way to only fill the target cell if 3 "search" cells contain specific, but different values. For example, have the target cell (A5) return "Nancy Pelosi" ONLY if A1="United State AND A2="California" AND A3=Santa Clara County" AND A4="San Francisco". Basically I would like to return a value, in this case house representatives, when the user selects the country, state, county, and city of the representatives. Kind of a search method to find your local representative by searching country, state, county, and city.


I hope this makes sense!
This comment was minimized by the moderator on the site
Good Day,
Please try methods mentioned in this article:
https://www.extendoffice.com/documents/excel/2440-excel-vlookup-multiple-criteria.html
Or you can post your question in our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
This comment was minimized by the moderator on the site
OMG....been trying to find a way to get to this answer for DAYS!!! THANK YOU!!!
This comment was minimized by the moderator on the site
Anyone have any ideas on this; I want to have a formula search for a combination of letters/numbers (part number) on another sheet. If found, pull a corresponding cell's number (dollar value)...thank you!
This comment was minimized by the moderator on the site
Good Day,
This array formula can help you:=CELL("address",INDEX(Sheet2!$D$4:$D$13,MATCH(A6,Sheet2!$D$4:$D$13,1))) + Ctrl + Shift + Enter
A6 is the cell contains the combination of letters/numbers you will search, and Sheet2!$D$4:$D$13 is the range you will search in.
This comment was minimized by the moderator on the site
think, a1= name, b1=number, c1=days. i want in another sheet a1= name, b1=number, c1= ?(i want days value). can i understood you.
This comment was minimized by the moderator on the site
Sorry I didn't get your question.
This comment was minimized by the moderator on the site
"Licensee Name: Walker, Charlene
308 E St South

Talladega, AL 35160

Phone: 256-362-4307"





I want to move data in 3 different cell/row "name" "address" " phone". How can I do that?
This comment was minimized by the moderator on the site
Good Day,
Do you mean moving data in 3 different specified cells or rows? I don't really get your point. Would be nice if you could provide screenshot of what you are trying to do. Thank you.
This comment was minimized by the moderator on the site
you may want to avoid pasting actual personal information. This looks like a breach of privacy.
This comment was minimized by the moderator on the site
Hi,


I'm trying to find something similar to this but I'm having trouble.


I have a list of addresses in Sheet 1, the whole address is in one cell. So for example A2, A3, A4 all contain a different address. Then in B2, B3, B4 etc I want to display a area code, "3" or "8" for example based on the postcode in the cell in column A.


I have a list of postcodes in Sheet 2 with the area code adjacent to them.


Ive tried with a VLOOKUP and a CELL but I cant fathom it out... if anyone can help I would be grateful......
This comment was minimized by the moderator on the site
Good day,
Would you provide a screenshot of your spreadsheet?
This comment was minimized by the moderator on the site
Please help. If a cell has a word in it show a number from another cell (example If n8=approved I want it give me the information from k8)
This comment was minimized by the moderator on the site
Hi Christina,
Please apply this formula =IF(ISNUMBER(SEARCH("approved",N8)),K8,"")
This comment was minimized by the moderator on the site
Hi. I'm trying to give a certain cell a value based on the text in another cell. E.g. if A3 shows the text "BCD" than A4 should display $10. How can I do this?
This comment was minimized by the moderator on the site
Good day,
Please enter this formula =IF(ISNUMBER(SEARCH("BCD",A3)),"$10","") in cell A4.
This comment was minimized by the moderator on the site
Good Morning Crystal,


I'm trying to use a formula in excel that will determine what another cell does based on which office destination I'm using. For example my offices have destinations of 7102, 7302, 7402, and 7502, while another has destinations of 7109, 7309, 7409, and 7509. I would like for excel to understand what to do to another cell based on what is in the office destination cell. Continued example, I want to use a formula to calculate 90 days out from an various effective dates, but the 90 day date is determined by the office destination. Thus, I need the cell to determine which effective date cell to use based on which office destination is in another cell. So let's say if A1 had 7102, 7302, 7402, or 7502, then cell C1 would default to use the effective date in cell B1 based on what office destination range is in A1. I'm hoping this is possible. Any help is appreciated and please let me know if you need more information.

Thank you.
This comment was minimized by the moderator on the site
Hi Danny,
Sorry I can’t help with this. Welcome to post any question in our forum: https://www.extendoffice.com/forum.html to get more Excel support from out Excel professional or other Excel fans.
Thanks for your comment.
This comment was minimized by the moderator on the site
Hi Danny - I was needing the same return as you. Did you happen to get any support with this? Any help would be great. Thanks!
This comment was minimized by the moderator on the site
Hi - How do I replace content in a cell, if it has anything in it at all, with content from another cell? Both cells have text, not numbers.
This comment was minimized by the moderator on the site
Hi denise,
Sorry I am not sure I got your question. Do you want to swap content of two cells?
Would be nice if you can explain again or provide screenshot of what you are trying to do.
Thanks for your comment.
This comment was minimized by the moderator on the site
Hi, 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"
This comment was minimized by the moderator on the site
nevermind, I figured it out, just nested a TON of =IF statements:
=IF(D3=$A$2,$B$2,IF(D3=$A$3,$B$3,IF(D3=$A$4,$B$4,IF(D3=$A$5,$B$5,IF(D3=$A$6,$B$6,IF(D3=$A$7,$B$7,IF(D3=$A$8,$B$8,IF(D3=$A$9,$B$9,IF(D3=$A$10,$B$10,IF(D3=$A$11,$B$11))))))))))
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations