Kako vrniti vrednost v drugi celici, če celica vsebuje določeno besedilo v Excelu?
Kot primer, prikazan spodaj, ko celica E6 vsebuje vrednost "Da", se celica F6 samodejno izpolni z vrednostjo "odobri". Če ste v E6 spremenili "Da" na "Ne" ali "Nevtralnost", bo vrednost v F6 takoj spremenjena v "Zavrni" ali "Ponovno premisli". Kako lahko to storite? V tem članku je zbranih nekaj uporabnih metod, ki vam jih bodo pomagale enostavno rešiti
Metoda B: Vrni vrednosti v drugi celici, če celica vsebuje različna besedila s formulo
Metoda C: Več klikov za enostavno vrnitev vrednosti v drugi celici, če celica vsebuje drugačna besedila
Vrni vrednost v drugi celici, če celica vsebuje določeno besedilo s formulo
Če želite vrniti vrednost v drugi celici, če celica vsebuje samo določeno besedilo, poskusite z naslednjo formulo. Če na primer B5 vsebuje "Da", vrnite "Odobri" v D5, drugače pa vrnite "Brez kvalifikacij". Naredite naslednje.
Izberite D5 in skopirajte spodnjo formulo vanjo ter pritisnite Vnesite tipko. Oglejte si posnetek zaslona:
Formula: Vrni vrednost v drugi celici, če celica vsebuje določeno besedilo
= IF (ŠTEVILO (ISKANJE ("Da",D5)), "Odobriti""Brez kvalifikacij")
Opombe:
1. V formuli „Da" D5"odobri"In"Brez kvalifikacij"Označite, da če celica B5 vsebuje besedilo" Da ", bo navedena celica zapolnjena z besedilom" odobri ", drugače pa bo izpolnjena z" Ni kvalificiranega ". Lahko jih spremenite glede na vaše potrebe.
2. Za vrnitev vrednosti iz drugih celic (kot sta K8 in K9) na podlagi določene vrednosti celice, uporabite to formulo:
= IF (ŠTEVILO (ISKANJE ("Da",D5))K8,K9)
Preprosto izberite celotne vrstice ali celotne vrstice v izboru glede na vrednost celice v določenem stolpcu:
O Izberite Specific Cells uporabnost Kutools za Excel vam lahko pomaga hitro izbrati celotne vrstice ali celotne vrstice v izboru na podlagi določene vrednosti celice v določenem stolpcu v Excelu. Prenesite celotno 60-dnevno brezplačno pot Kutools za Excel zdaj!
Vrni vrednosti v drugi celici, če celica vsebuje različna besedila s formulo
Ta razdelek vam bo prikazal formulo za vrnitev vrednosti v drugi celici, če celica vsebuje drugačno besedilo v Excelu.
1. Ustvariti morate tabelo s posebnimi vrednostmi in vrnjenimi vrednostmi, ki se nahajajo ločeno v dveh stolpcih. Oglejte si posnetek zaslona:
2. Izberite prazno celico za vrnitev vrednosti, vanjo vnesite spodnjo formulo in pritisnite tipko Vnesite ključ, da dobite rezultat. Oglejte si posnetek zaslona:
Formula: Vrni vrednosti v drugi celici, če celica vsebuje drugačna besedila
= VLOGA (E6,B5: C7,2, FALSE)
Opombe:
V formuli: E6 je celica vsebuje določeno vrednost, na podlagi katere boste vrnili vrednost, B5: C7 je obseg stolpcev, ki vsebuje posebne vrednosti in vrnjene vrednosti, 2 število pomeni, da vrnjene vrednosti, ki se nahajajo v drugem stolpcu v obsegu tabele.
Od zdaj naprej se bo pri spreminjanju vrednosti v E6 na določeno vrednost takoj vrnila v F6.
Enostavno vrnite vrednosti v drugi celici, če celica vsebuje drugačna besedila
Pravzaprav lahko zgornji problem rešite na lažji način. The Poiščite vrednost na seznamu uporabnost Kutools za Excel vam lahko pomaga, da ga dosežete z le nekaj kliki, ne da bi si zapomnili formulo.
1. Enako kot pri zgornji metodi morate tudi ustvariti tabelo s posebnimi vrednostmi in vrnjenimi vrednostmi, ki jih locirate ločeno v dveh stolpcih.
2. Izberite prazno celico, da izpišete rezultat (tukaj izberem F6), in kliknite Kutools > Formula Helper > Formula Helper. Oglejte si posnetek zaslona:
3. V Ljubljani Formula Helper pogovorno okno, nastavite na naslednji način:
- 3.1 V Izberite formulo poiščite in izberite Poiščite vrednost na seznamu;
nasveti: Lahko preverite filter v polje za besedilo vnesite določeno besedo, da hitro filtrirate formulo. - 3.2 V Tabela_ matrika izberite tabelo brez glav, ki ste jo ustvarili v 1. koraku;
- 3.2 V Lookup_value polje izberite polje vsebuje določeno vrednost, na podlagi katere boste vrnili vrednost;
- 3.3 V Stolpec v stolpcu določite stolpec, iz katerega boste vrnili ujemajočo se vrednost. Ali pa lahko v polje za besedilo vnesete številko stolpca neposredno, kot jo potrebujete.
- 3.4 Kliknite na OK . Oglejte si posnetek zaslona:
Od zdaj naprej se bo pri spreminjanju vrednosti v E6 na določeno vrednost takoj vrnila v F6. Glej rezultat kot spodaj:
Če želite imeti brezplačno (30-dnevno) preskusno različico tega pripomočka, kliknite, če ga želite prenestiin nato nadaljujte z uporabo postopka v skladu z zgornjimi koraki.

Ni vam več treba zapomniti formul in kod VBA, odslej si privoščite počitek v možganih.
Zapletene in ponavljajoče se operacije je mogoče enkrat obdelati v nekaj sekundah.
Vsak dan zmanjšajte na tisoče operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
Postanite strokovnjak za Excel v 3 minutah, vam pomaga hitro prepoznati in napredovanje za dvig plače.
Izbira 110,000 visoko učinkovitih ljudi in 300+ svetovno priznanih podjetij.
Naj bodo vaši 39.0 USD vredni več kot 4000.0 USD usposabljanja drugih.
Popolnoma brezplačna preizkusna različica 30-dnevna. 60-dnevno jamstvo za vračilo denarja brez razloga.
You are guest
or post as a guest, but your post won't be published automatically.
-
To post as a guest, your comment is unpublished.· 6 months agoHi, I am trying to work out a formula so that a cell prepopulates with a number between 1 and 5 depending on where an x is marked in a cell on the same line. For instance E13 has an x in it which means J13 shows a value of 1 or G13 has an x in it and J13 then shows a value of 3, etc. Thank you
-
To post as a guest, your comment is unpublished.Hi There, I am trying to use an If Then formula (I think). If cell H2='Returned' then I would like cell G2(which is a value) to equal cell E2 which is a value. If cell H4=Confirmed then I would like cell G2 to be blank. Can anyone help? Thank you!
-
To post as a guest, your comment is unpublished.Please could I have some assistance. Is it possible to enter a number into a cell and return a specific amount from another cell. But the difficulty is that I have this scenario. I have 10 Zones, each zone has 48 cells with figures. What I want to do is enter a zone into a cell for the table to update with the figures from that specific column. Example. I enter zone 6 onto my spreadsheet, in return I want all 48 cells to update with the specific amounts from zone 6 column. Again when I change the zone to 4 I want the column to update with the new figures from Zone 4. Anyone who can help would be greatly appreciated
-
To post as a guest, your comment is unpublished.Hello. Is there a way I can do the following: Based on one column, copy the data from another column into a new column. Example: a horse race with 5 horses. Column A lists the horses A1 through A5 in their post order (1 through 5). Column B with their rank (for example: B1=3; B2=1; B3=2; B4=5 & B5=4.). Column C list the names of the horses (once again in their post order that matches Column A) What I'm looking to do have column D list the horse numbers (from column A) in the order of their rank. So column D would be D1 = 2; D2 = 3; D3 = 1; D4 = 5; D5 = 4. Is this possible? Thanks - J.
-
To post as a guest, your comment is unpublished.Hi Joe Monk,
Please select cell D1, enter this formula =INDEX($A$1:$A$5,MATCH(C1,$B$1:$B$5,0)) into it and press the Enter key. Then drag the Fill Handle of the first result cell all the way down to get the other horse rank. Hope I can help. Thank you.-
To post as a guest, your comment is unpublished.Crystal, Thank you very much!! Am I correct in assuming that there is no way to have that formula show more than one number in the cell? It works perfectly as long as no two horses have the same rank. If there is a tie (1 in B2 & 1 in B3 for example), it only returns a value of 2 in D1. I have a feeling I'm asking for something beyond the spreadsheet's pay grade. :-) Thanks again for your response & the formula. - Joe
-
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hello,
I'm trying to run something like this: if column a has numbers in a cell
1
2
3
I want it to search in the file for all the cells in column A that have 1 to name it as Good and 2 to be named as Bad, and 3 has NA.
I have what you showed working, but how do i extend it? =IF(ISNUMBER(SEARCH("1",A2)),"Good"),IF(ISNUMBER(SEARCH("2",A2)),"BAD")
If i just do the first part of Good it works, but when i try to add the formula together i get: #Value!-
To post as a guest, your comment is unpublished.got it:
=IF(A6=1,"Good",IF(A6=2,"Bad",IF(A6=3,"NA",36))) Not sure what the 36 stands for, but i noticed if none of those numbers meet then it takes 36
-
-
To post as a guest, your comment is unpublished.If A1 is 10000 then B1 reflect A1 if i put Yes In c1 Then B1 shoud be Zero. If i put No Then B1 need to show A1
How can i do this?
Currently Im using this formula but I think half of the way its not showing B1
=IF(ISNUMBER(SEARCH("Yes",C1"0","a1")
in c1 put Yes then b1 need to Zero its done. But when in put No in c1its not showing A1 Value.
Kindly guide me -
To post as a guest, your comment is unpublished.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.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.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.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.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.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.
-
To post as a guest, your comment is unpublished.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.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.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.
-
-
To post as a guest, your comment is unpublished.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.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.Good day,
Please enter this formula =IF(ISNUMBER(SEARCH("BCD",A3)),"$10","") in cell A4.-
To post as a guest, your comment is unpublished.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.
-
To post as a guest, your comment is unpublished.
-
-
-
-
To post as a guest, your comment is unpublished.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.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."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.
-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.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.
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.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.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.
-
-
To post as a guest, your comment is unpublished.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.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.
-
To post as a guest, your comment is unpublished.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.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.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.hai, anybody thr to help me in excel?
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.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.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.
-
To post as a guest, your comment is unpublished.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.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.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.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.
-
To post as a guest, your comment is unpublished.
-
-
-