跳到主要內容

如果一個單元格包含Excel中的某些文本,如何在另一個單元格中返回值?

如下例所示,當單元格 E6 包含值“是”時,單元格 F6 將自動填充值“批准”。 如果在 E6 中將“是”更改為“否”或“中立”,F6 中的值將立即更改為“拒絕”或“重新考慮”。 你怎麼做才能實現它? 本文收集了一些有用的方法來幫助您輕鬆解決它。


如果一個單元格包含帶有公式的某些文本,則在另一個單元格中返回值

如果一個單元格僅包含特定文本,則要在另一個單元格中返回值,請嘗試以下公式。 例如,如果B5包含“是”,則在D5中返回“批准”,否則,返回“不合格”。 請做如下.

選擇D5,然後將以下公式複製到其中,然後按 Enter 鍵。 看截圖:

公式: 如果一個單元格包含某些文本,則在另一個單元格中返回值

= IF(ISNUMBER(SEARCH(“",D5)),”批准“,”沒有資格")

筆記:

1.在公式中,“D5批准“和”沒有資格”表示如果單元格B5包含文本“是”,則將在指定的單元格中填充文本“批准”,否則,將用“否”填充。 您可以根據需要進行更改。

2.要基於指定的單元格值從另一個單元格(例如K8和K9)返回值,請使用以下公式:

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

根據特定列中的單元格值輕鬆選擇整個行或選擇中的整個行:

選擇特定的單元格 的效用 Excel的Kutools 可以幫助您根據Excel中特定列中的特定單元格值快速選擇整個行或所選內容中的整個行。  立即下載Kutools for Excel的全功能60天免費試用版!


如果一個單元格包含帶有公式的不同文本,則返回另一個單元格中的值

如果單元格在Excel中包含不同的文本,本節將向您顯示在另一個單元格中返回值的公式。

1.您需要創建一個具有特定值的表,並將返回值分別放在兩列中。 看截圖:

2.選擇一個空白單元格以返回值,在其中鍵入以下公式,然後按 Enter 獲得結果的關鍵。 看截圖:

公式: 如果一個單元格包含不同的文本,則返回另一個單元格中的值

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

筆記:

在公式, E6 是單元格包含您將基於其返回值的特定值, B5:C7 是包含特定值和返回值的列範圍, 2 number表示返回值位於表範圍的第二列。

從現在開始,將E6中的值更改為特定值時,其對應的值將立即在F6中返回。


如果一個單元格包含不同的文本,則輕鬆在另一個單元格中返回值

實際上,您可以通過一種更簡單的方法解決上述問題。 的 在列表中查找值 的效用 Excel的Kutools 只需點擊幾下就可以幫助您實現目標,而無需記住公式。

1.與上述方法相同,您還需要創建一個具有特定值的表,並將返回值分別放置在兩列中。

2.選擇一個空白單元格以輸出結果(此處選擇F6),然後單擊 庫工具 > 公式助手 > 公式助手。 看截圖:

3。 在裡面 公式助手 對話框,請進行如下配置:

  • 3.1在 選擇一個公式 框,找到並選擇 在列表中查找值;
    保養竅門:您可以檢查 篩選 框,在文本框中輸入特定單詞以快速過濾公式。
  • 3.2在 表格數組 框中,選擇不包含您在步驟1中創建的標題的表;
  • 3.2在 查找值 框,選擇包含您將基於其返回值的特定值的單元格;
  • 3.3在 框,指定您要從中返回匹配值的列。 或者,您可以根據需要直接在文本框中輸入列號。
  • 3.4點擊 OK 按鈕。 看截圖:

從現在開始,將E6中的值更改為特定值時,其對應的值將立即在F6中返回。 查看結果如下:

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


Kutools for Excel- 幫助您始終提前完成工作,有更多時間享受生活
您是否經常發現自己正在趕上工作,缺乏時間為自己和家人度過?  Excel的Kutools 可以幫你處理 80% Excel 拼圖,提高 80% 的工作效率,讓您有更多時間照顧家人,享受生活。
300 個高級工具,適用於 1500 個工作場景,讓您的工作變得比以往更加輕鬆。
從現在起,不再需要記住公式和VBA代碼,讓您的大腦休息一下。
複雜和重複的操作可以在幾秒鐘內完成一次處理。
每天減少成千上萬的鍵盤和鼠標操作,現在告別職業病。
在3分鐘內成為Excel專家,幫助您快速獲得認可並提薪。
110,000名高效人才和300多家世界知名公司的選擇。
使您的$ 39.0的價值超過$ 4000.0的他人培訓。
全功能免費試用 30 天。 60 天無理由退款保證。

Comments (98)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What would I need to add to this formula (Method A) to get a third BLANK option? And where would it go?
Example:
If the cell I'm searching is blank, I would like it to return no value in the other cell. I do not want the "if false" value to populate.
This comment was minimized by the moderator on the site
i want a cell to automatically choose a number based on the drop-down option in the cell before, e.g drop-down, half meal, full meal, if I select half meal drop-down, the next cell should show 50% while the full meal drop-down will reflect 100%. please help
This comment was minimized by the moderator on the site
Hi Josh,
In the cell where you want to output the percentage, enter the following formula and press the Enter key. Hope I can help.
=IF(C15="half meal", "50%", IF(C15="full meal", "100%", ""))
This comment was minimized by the moderator on the site
Is it possible to return a certain value in 1 column based on another?


What I currently have: On a differnt tab (same worksheet):
Column A Column B
Location Name Dept Location Name Location Code
Miami 4455 Miami 123


What I want to happen:
Location Name Dept
Miami 11234455

In Coumn B, I want it to first add a 1, then take what is in Column A, see what is the Location Code on then different tab, insert that location code, and add that to the front of the current data in Column B.

1 + 123 + 4455
This comment was minimized by the moderator on the site
Hi Jen Rader,
You can apply the following formula to get it done.
=IFERROR(1&VLOOKUP(A9,Sheet2!$A$2:$B$5,2, 0)&VLOOKUP(Sheet1!A9,Sheet1!$A$2:$B$5,2, 0),"")
In this formula,
1. A9 is the cell contains the value you want to return values based on;
2. Sheet2 is the tab name contains the "Location Code"; If the tab name contains space, please enclose the tab name in single quotes, such as 'tab name'.
3. Sheet1 is the tab name contains the "Dept";
4. $A$2:$B$5 is the range containing the table data (include both search values and return values ).
See screenshots below:
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/return1.png
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/return2.png
This comment was minimized by the moderator on the site
OMG Crystal! You are amazing! Show me your ways! Thank you so much - it worked exactly like I was hoping!
This comment was minimized by the moderator on the site
I am trying to find out the formula to copy the number shown in B2 on cell D2 if the Cell C2 has a letter "S". Can anyone help me?
This comment was minimized by the moderator on the site
Hi Aurora,
Please enter the following formula in cell D2.
=IF(ISNUMBER(SEARCH("S",C2)),B2,"No qualify")
This comment was minimized by the moderator on the site
Bonjour à tous, j'ai un tableau Excel et j'aimerais réaliser via une macro l'opération suivante: si une cellule contient une date, me renvoyer une valeur dans une autre cellule. Pourriez vous m'aider s'il vous plait? Je vous remercie par avance
This comment was minimized by the moderator on the site
STALLS D 25 Palais - Stalls SR
STALLS M 19 Palais - Stalls SR
LOUNGE E 22 Palais - Lounge SR
LOUNGE G 23 Palais - Lounge SL
ORCH K 40 Palais - Orchestra SR
For each line in a spreadsheet I need to evaluate against a table like above and find the value that matches all of the first 3 values.
Normally I would use sumifs for this type of problem but the answer is a text string so it returns 0
This comment was minimized by the moderator on the site
I need a formula that makes column N equal the text “OTO” if column K equals MCS TRAINING how do I do that???
This comment was minimized by the moderator on the site
Hi Abby,Please apply the following formula in the cells of column N:=IF(ISNUMBER(MATCH("MCS TRAINING",K1)),"OTO","NULL")
This comment was minimized by the moderator on the site
Hi - This is my formula that I am working with.  Currently it works, but where B21 is being returned, the value of B21 is a link to another place altogether.  I want to keep the display text but would like to include the link as well.  Can this be done?  =IF(ISNUMBER(SEARCH("DATA_MAPPING",general_report!AJ21)),general_report!B21,"")
This comment was minimized by the moderator on the site
Is it possible to split the return value to a different tab? IE: columns B and C are located on one tab, and values in columns E and F are located on a different tab?
This comment was minimized by the moderator on the site
Hi

Is it possible to populate another cell, not the cell with the formula.

Example
A1 Yes
A2 3
A3 Formula searches A1, Grabs value at A2 and if Yes Paste it in A4
A4 3 
This comment was minimized by the moderator on the site
Hi,
Sorry can't help you with the problem. I suggest you post the problem to the forum below to get help from other Excel enthusiasts.
https://www.extendoffice.com/forum/kutools-for-excel.html 
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations