팁 : 다른 언어는 Google 번역입니다. 방문하실 수 있습니다. English 이 링크의 버전.
로그인
x
or
x
x
회원가입
x

or

셀에 Excel에서 특정 텍스트가 포함되어 있으면 다른 셀에서 값을 반환하는 방법?

아래 예제와 같이 E6 셀에 값 "Yes"가 포함되어 있으면 셀 F6에 값 "approve"가 자동으로 채워집니다. E6에서 "Yes"를 "No"또는 "Neutrality"로 변경하면 F6의 값이 즉시 "거부"또는 "재검색"으로 변경됩니다. 그것을 성취하기 위해 어떻게 할 수 있습니까? 이 기사는 쉽게 해결할 수있는 유용한 방법을 모은다.


특정 열의 셀 값을 기준으로 선택에서 전체 행 또는 전체 행을 쉽게 선택할 수 있습니다.

특정 셀 선택 ~의 유용성 Excel 용 Kutools Excel에서 특정 열의 특정 셀 값을 기반으로 선택 영역에서 전체 행 또는 전체 행을 빠르게 선택할 수 있습니다. Excel 용 Kutools의 60-day 무료 트레일 전체 기능을 지금 다운로드하십시오!


셀에 수식이있는 특정 텍스트가 포함 된 경우 다른 셀의 반환 값

셀에 특정 텍스트 만 들어있는 경우 다른 셀의 값을 반환하려면 다음 수식을 사용하십시오. 예를 들어, B5에 "Yes"가 포함되어 있으면 D5에서 "Approve"를 리턴하고, 그렇지 않으면 "No Qualify"를 리턴하십시오. 다음과 같이하십시오..

D5을 선택하고 아래 수식을 복사 한 다음 엔터 버튼 키. 스크린 샷보기 :

수식 : 셀에 특정 텍스트가 포함 된 경우 다른 셀의 반환 값

= IF (ISNUMBER (SEARCH ( "",D5)), "승인","자격 없음")

노트:

1. 공식에서 "" D5"승인"및"자격 없음B5 셀에 텍스트 "예"가 포함되어 있으면 지정된 셀에 텍스트 "승인"이 채워지고 그렇지 않으면 "제한 없음"으로 채워짐을 나타냅니다. 필요에 따라 변경할 수 있습니다.

2. 지정된 셀 값에 따라 다른 셀 (예 : K8 및 K9)에서 값을 반환하려면 다음 수식을 사용하십시오.

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

Office에서 탭 편집 및 찾아보기
--- 작업이 훨씬 쉬워집니다
사무실 탭 여러 문서를 탐색하고 편집 할 때 작업 시간의 50 %를 절약 할 수 있습니다.
믿을 수 없는! 두 개 이상의 문서 작업은 단일 문서 작업보다 즐겁습니다.
매일 수천 건의 키보드 및 마우스 작동을 줄이고 지금 직업병에 이별하십시오.
Office Tab의 인터페이스는 인터넷 브라우저보다 훨씬 강력하고 효율적입니다.

셀에 수식이있는 다른 텍스트가 포함 된 경우 다른 셀의 값 반환

이 섹션에서는 셀에 Excel에서 다른 텍스트가 포함되어있는 경우 다른 셀의 값을 반환하는 수식을 보여줍니다.

1. 별도의 두 열에 위치한 특정 값과 반환 값을 사용하여 테이블을 만들어야합니다. 스크린 샷보기 :

2. 값을 반환 할 빈 셀을 선택하고 아래 수식을 입력 한 다음 엔터 버튼 키를 눌러 결과를 가져옵니다. 스크린 샷보기 :

수식 : 셀에 다른 텍스트가 들어 있으면 다른 셀의 값을 반환합니다.

= VLOOKUP (E6,B5 : C7,2,그릇된)

노트:

이 식에서, E6 셀에 값을 반환 할 특정 값이 포함되어 있는지, B5 : C7 특정 값과 반환 값을 포함하는 열 범위입니다. 2 숫자는 테이블 범위의 두 번째 열에있는 반환 값을 의미합니다.

이제 E6의 값을 특정 값으로 변경하면 해당 값이 즉시 F6에 반환됩니다.


셀에 다른 텍스트가 있으면 다른 셀의 값을 쉽게 반환합니다.

사실, 위의 문제는 더 쉬운 방법으로 해결할 수 있습니다. 그만큼 목록에서 값을 찾습니다. ~의 유용성 Excel 용 Kutools 수식을 기억하지 않고 단 몇 번의 클릭만으로 달성 할 수 있습니다.

1. 위의 메소드와 동일하게, 두 개의 컬럼에서 개별적으로 위치하는 특정 값과 리턴 값으로 테이블을 작성해야합니다.

2. 특정 셀 값을 기반으로 자동으로 값을 반환 할 셀을 선택하고 확인을 클릭합니다. Kutools > 수식 도우미 > 목록에서 값을 찾습니다.. 스크린 샷보기 :

3. 그런 다음 수식 도우미 대화 상자가 나타납니다.

  • 3.1 Table_array 상자에서 1 단계에서 생성 한 헤더가없는 표를 선택하십시오.
  • 3.2 Look_value 상자에서 셀을 선택하면 값을 반환 할 특정 값이 포함됩니다.
  • 3.3 상자에서 자동으로 반환 할 값이 들어있는 열 범위 (머리글 없음)를 선택합니다.
  • 3.4 OK 단추. 스크린 샷보기 :

이제 E6의 값을 특정 값으로 변경하면 해당 값이 즉시 F6에 반환됩니다. 아래의 결과를보십시오 :

팁. 이 유틸리티의 무료 평가판 (60-day)을 원한다면, 그것을 다운로드하려면 클릭하십시오, 위의 단계에 따라 작업을 적용하십시오.


Excel 용 Kutools - 시간을 앞두고 항상 일을 끝내고 인생을 즐기는 데 더 많은 시간을 할애 해줍니다.
자신과 가족을 위해 보내는 시간이 부족하고 일을 따라 잡는 것을 자주 보십니까? Excel 용 Kutools 80 % Excel 퍼즐을 다루고 80 % 업무 효율성을 개선하고 가족을 돌보며 삶을 즐기는 데 더 많은 시간을 할애 할 수 있습니다.
300 작업 시나리오를위한 1500 고급 도구를 사용하면 훨씬 쉽게 작업을 수행 할 수 있습니다.
더 이상 수식과 VBA 코드를 기억할 필요가 없으므로 이제부터는 뇌를 편안하게 쉬십시오.
복잡하고 반복되는 작업은 1 초 단위로 처리 할 수 ​​있습니다.
매일 수천 건의 키보드 및 마우스 작동을 줄이고 이제 직업병에 대한 작별 인사를하십시오.
3 분에 Excel 전문가가되어 신속하게 인정 받고 유료 인상 프로모션을 진행할 수 있습니다.
110,000은 매우 효과적인 사람들과 300 + 세계적으로 유명한 기업의 선택입니다.
다른 사람들에 대한 $ 39.0 이상의 가치가있는 $ 4000.0을 만드십시오.
모든 기능 무료 평가판 30 일. 이유없이 60-Day 환불 보증.

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Lorraine · 2 months ago
    Hi There,
    I need to match up cells in different worksheets.
    So, if client number 12345 (Book1) = Client number 12345 (Book 2), copy the email address in the same row of Book 1 over to Book 2. Two different cells, same row. The defining factor is the Client number and using this, I need the email address copied to the new worksheet. Thanks.
  • To post as a guest, your comment is unpublished.
    Emily K · 2 months ago
    I am trying to input this formula. Please help!
    If S2 contains A, E, H, or K then K2 should be 0
    If S2 contains D, I, or J then K2 should be((H2-I2)*.25)
    If S2 contains B, C, F, G, or L then K2 should be((H2-I2)*.80)
    • To post as a guest, your comment is unpublished.
      crystal · 1 days ago
      Hi Emily,
      Sorry can't help you with that.
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Stephen · 3 months ago
    I am using the the formula =if(ISNUMBER(SEARCH("1",B3)),"250","0").

    The idea is that is the cell b3 contains is will dispaly 250 as it the cost of the room. It all works great, however my issue is when I try to sum all the column it gives me 0 even though some fields have 250 in them. Any ideas why?
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Stephen,
      I try to sum the values (including the returned value 250) as you mentioned, and it returns the correct result in my case.
      Would you please provide a screenshot of your spreadsheet?
  • To post as a guest, your comment is unpublished.
    Joanna · 3 months ago
    Hi, I have three columns and if there is a "Y" in the third column then I want it to output a "Y" in the other two columns before it so for eg:
    A B C
    Y
    Y Y Y
    Y
    Y Y Y

    Any help much appreciated!
  • To post as a guest, your comment is unpublished.
    Khaled · 6 months ago
    Hello,
    How can enter a formula to search a text in a certain column in the entire sheet, if it finds it, it enters a value on the same line on another column e.g search for starbucks in Column d, then enter 70500 on the same line column c
  • To post as a guest, your comment is unpublished.
    Liam Walker · 8 months ago
    Hi, 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.
    mariana · 1 years ago
    what 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.
    Gavin · 1 years ago
    HI, 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.
    Chris · 1 years ago
    Hi,

    I am trying to get the below formula to work.
    =IF(ISNUMBER(SEARCH({"Name","Code"},A1)),CONCATENATE(K3,"xx",L3,"hi"))

    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?


    Thanks.
  • To post as a guest, your comment is unpublished.
    Jesse Eastman · 1 years ago
    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"
    • To post as a guest, your comment is unpublished.
      Jesse Eastman · 1 years ago
      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))))))))))
  • To post as a guest, your comment is unpublished.
    denise · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Ruud · 1 years ago
    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?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Please enter this formula =IF(ISNUMBER(SEARCH("BCD",A3)),"$10","") in cell A4.
      • To post as a guest, your comment is unpublished.
        Danny · 1 years ago
        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.
        • To post as a guest, your comment is unpublished.
          Jenni · 11 months ago
          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!
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          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.
  • To post as a guest, your comment is unpublished.
    Christina · 1 years ago
    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)
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Christina,
      Please apply this formula =IF(ISNUMBER(SEARCH("approved",N8)),K8,"")
  • To post as a guest, your comment is unpublished.
    J.E · 1 years ago
    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......
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Would you provide a screenshot of your spreadsheet?
  • To post as a guest, your comment is unpublished.
    Matt Mahfuj · 1 years ago
    "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?
    • To post as a guest, your comment is unpublished.
      DL · 1 years ago
      you may want to avoid pasting actual personal information. This looks like a breach of privacy.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      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.
  • To post as a guest, your comment is unpublished.
    goutam · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    J · 1 years ago
    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!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Michelle · 1 years ago
    OMG....been trying to find a way to get to this answer for DAYS!!! THANK YOU!!!
  • To post as a guest, your comment is unpublished.
    Giancarlo · 1 years ago
    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!
  • To post as a guest, your comment is unpublished.
    pamela · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Sorry I didn't get your point. Would be nice if you could provide screenshot of what you are trying to do.
  • To post as a guest, your comment is unpublished.
    Kim Stevens · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Kim Stevens · 1 years ago
      Tried this and it keeps coming up 'false'
      =IF(E1="C & I","C & I (MVA)",G1+12=H1)
    • To post as a guest, your comment is unpublished.
      Kim Stevens · 1 years ago
      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.
      • To post as a guest, your comment is unpublished.
        crystal · 1 years ago
        Dear Kim,
        This formula can help you: =IF(E1:E12="C & I (MVA)", G1+12, " ")
  • To post as a guest, your comment is unpublished.
    Kim · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Kim · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Luke · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      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.
  • To post as a guest, your comment is unpublished.
    SS Reddy V · 2 years ago
    hai, anybody thr to help me in excel?
  • To post as a guest, your comment is unpublished.
    Shyam · 2 years ago
    hai anybody there to help
  • To post as a guest, your comment is unpublished.
    Dave · 2 years ago
    Please help. If a cell has a word in it show a number from another cell
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Dave,

      Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
  • To post as a guest, your comment is unpublished.
    Kris · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      v · 2 years ago
      Sorry, hope I understood your query..


      =IF(OR(A2<>"",B2<>"",C2<>"",D2<>"",E2<>"",F2<>""),RANDBETWEEN(1,10000000000),"")
  • To post as a guest, your comment is unpublished.
    Brandon · 2 years ago
    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.
    • To post as a guest, your comment is unpublished.
      V · 2 years ago
      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))
    • To post as a guest, your comment is unpublished.
      Rob · 2 years ago
      If you are still having trouble then try this example: =IF(ISNUMBER(SEARCH("Yes",A1)),"approve",IF(ISNUMBER(SEARCH("NO",A1)),"unapproved","")
      • To post as a guest, your comment is unpublished.
        Aakash · 2 years ago
        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.
        • To post as a guest, your comment is unpublished.
          Rob · 2 years ago
          I did this for 4 values, i just copy and pasted from 'IF' and changed what I wanted the cell to read.
    • To post as a guest, your comment is unpublished.
      Gary · 2 years ago
      I'm wondering the same question, can't get it to work.
      =IF(ISNUMBER(SEARCH("A,C",B:B)),"Alameda,Chicago","") something like this
      • To post as a guest, your comment is unpublished.
        John · 2 years ago
        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
        • To post as a guest, your comment is unpublished.
          V · 2 years ago
          @Gary,
          What are you trying to accomplish?


          @John,
          =IF(A1="YES","APPROVED",IF(A1="NO","DENY",""))
        • To post as a guest, your comment is unpublished.
          Michael · 2 years ago
          Has no one found a solution for this? It would be very helpful.