Skip to main content

Kutools for Office — 一套工具，五種功能。完成更多工作。

如何在Excel中如果某個值存在於特定範圍內時返回一個值？

Author Siluvia Last modified
return a value if a given value exists

在Excel中處理數據時，通常需要確定特定值是否位於定義的範圍內，如果是，則從與該條目相鄰的單元格中檢索值。例如，如左側截圖所示，如果要在列表或範圍內查找數字5，您可能希望自動返回對應的相鄰值——這種情況對於查找產品ID、檢索用戶信息或匹配代碼與值非常有用，無需手動搜索。

如果給定值存在於某個範圍內，則返回一個值

如果給定值存在於某個範圍內，則使用VLOOKUP函數返回一個值

要快速從數據表或範圍內檢索與特定條目相關聯的值，VLOOKUP函數提供了一個簡單的解決方案。

如果您的查找列（您在其中搜索值的列）是數據範圍的最左列，並且您想從其右側的列中返回數據，此方法特別有效。它通常用於搜索代碼、名稱、ID或參考編號並輕鬆獲取相關詳細信息。

1. 選擇您希望結果顯示的空白單元格。在公式欄中輸入以下公式：

=VLOOKUP(E2,A2:C8,3,TRUE)

按下Enter鍵以執行公式。見截圖：

apply a formula to return a value if a given value exists

在此示例中，如果數字5（在E2單元格中）位於A列指定的數字範圍內（例如，在4到6之間），Excel將查找該值並立即從A2:C8範圍的第三列（C列）填充相應的值到您選擇的單元格中。在插圖中，返回“Addin 012”，因為數字5位於4-6範圍內。

注意：在公式中，E2指的是查找值，A2:C8是包含查找值和返回值的數據範圍，而3指定返回值應該來自指定範圍的第三列。根據您的工作表適當調整這些引用。

提示與陷阱：

  • 確保查找範圍（A2:C8）包含查找列和返回列。
  • 當使用帶有TRUE參數的VLOOKUP時，查找列必須按升序排列，否則可能會得到意外結果。
  • 對於精確匹配，使用FALSE作為第四個參數；但對於範圍查找（如本例所示），保持為TRUE。
  • 如果您的數據經常變動，請仔細檢查引用以避免錯位錯誤。

如果給定值存在於某個範圍內，則使用INDEX和MATCH函數返回一個值

INDEX和MATCH組合是一種靈活的方法，用於在給定值存在於某個範圍內時返回值。與VLOOKUP不同，INDEX和MATCH可以在任何列中查找值，並從其他任何列返回結果，不論順序如何。如果您查找的列不是最左列，或者需要在數據結構中更靈活適應時，這一點尤其有用。

1. 選擇您希望結果出現的空白單元格（例如F2）。在公式欄中輸入以下公式：

=INDEX(C2:C8, MATCH(E2, A2:A8,1))

按下Enter確認公式。

逐步解釋：
  • MATCH(E2, A2:A8, 1) 在A列中查找小於或等於E2的最大值的位置。（這要求A列按升序排列。）
  • INDEX(C2:C8, ...) 返回由MATCH找到的行號的C列中的值。

此公式在A2:A8範圍內查找E2的值。如果找到該值（例如，5在其中一行介於4和6之間），MATCH函數返回其相對位置，INDEX從C2:C8相應行中提取值。“1”表示近似匹配，因此請確保您的查找範圍已正確排序。

提示:
  • 如果您想要精確匹配，請在MATCH函數中使用0作為第三個參數。
  • INDEX和MATCH還支持垂直和水平數據方向。
  • 如果找不到該值，公式將返回#N/A；考慮使用IFERROR進行友好輸出。

如果某個值存在於特定範圍內，則使用XLOOKUP函數返回一個值

XLOOKUP函數是在Excel365和Excel2019中查找值的一種現代替代方案。XLOOKUP克服了VLOOKUP的許多限制，例如查找列位置的限制以及自動精確/近似匹配。

1. 在期望的輸出單元格中（例如F2），輸入以下公式：

=XLOOKUP(1, (E2>=A2:A8)*(E2<=B2:B8), C2:C8)

輸入公式後，按下Enter鍵即可在選定單元格中看到結果。

逐步解釋：
  • (E2>=A2:A8) 檢查E2是否大於或等於A列中的每個值。
  • (E2<=B2:B8) 檢查E2是否小於或等於B列中的每個值。
  • 將這兩個條件相乘會生成一個由1和0組成的數組，其中1表示E2在該行的A和B之間。
  • XLOOKUP(1, ..., C2:C8) 查找第一個1並返回C列中的相應值。
提示和限制:
  • 如果插入或移動了列，XLOOKUP會動態調整，而VLOOKUP則使用固定列號。
  • 適用於垂直和水平數據。
  • 需要Excel 365或2021；對於舊版本，請使用上述其他方法。
a screenshot of kutools for excel ai

使用 Kutools AI 解鎖 Excel 的魔法

  • 智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
  • 自訂公式：生成量身定制的公式，簡化您的工作流程。
  • VBA 編碼：輕鬆編寫和實現 VBA 代碼。
  • 公式解釋：輕鬆理解複雜的公式。
  • 文本翻譯：打破電子表格中的語言障礙。
通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

相關文章：

最佳 Office 生產力工具

🤖 Kutools AI 助手：以智能執行為基礎，革新數據分析 ｜生成程式碼 ｜ 創建自訂公式 ｜ 分析數據並生成圖表 ｜ 調用 Kutools 增強函數…
熱門功能：查找、選取項目的背景色或標記重複值 ｜ 刪除空行 ｜ 合併列或單元格且不遺失數據 ｜ 四捨五入（免公式）...
高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
高級下拉列表：快速創建下拉列表 ｜依賴型下拉列表 ｜ 多選下拉列表...
列管理器：添加指定數量的列 ｜ 移動列 ｜ 切換隱藏列的顯示狀態 ｜ 比較區域及列...
精選功能：網格聚焦 ｜ 設計檢視 ｜ 增強編輯欄 ｜ 工作簿及工作表管理器 ｜ 資源庫（快捷文本） ｜ 日期提取器 ｜ 合併資料 ｜ 加密/解密儲存格 ｜ 按列表發送電子郵件 ｜ 超級篩選 ｜ 特殊篩選（篩選粗體/傾斜/刪除線...）...
15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表 類型（甘特圖等）｜40+ 實用 公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多
使用 Kutools，語言任你選 — 支援英語、西班牙語、德語、法語、中文及超過40 種語言！

運用 Kutools for Excel，全面提升您的 Excel 技能，體驗前所未有的高效。 Kutools for Excel 提供超過300 項進階功能，讓您提升工作效率、節省時間。 點此尋找您最需要的功能...

Office Tab 為 Office 帶來分頁介面，讓您的工作更加輕鬆簡單

  • 在 Word、Excel、PowerPoint 中啟用分頁編輯與閱讀。
  • 在同一視窗的新分頁中打開與創建多份文件，而非開啟新視窗。
  • 提升您的生產力50%，每日可幫您減少數百次鼠標點擊！

所有 Kutools 外掛，一次安裝

Kutools for Office 套裝整合了 Excel、Word、Outlook 和 PowerPoint 的外掛，外加 Office Tab Pro，非常適合需要跨 Office 應用程式協同作業的團隊。

Excel Word Outlook Tabs PowerPoint
  • 全合一套裝 — Excel、Word、Outlook及 PowerPoint 外掛 + Office Tab Pro
  • 一鍵安裝，一份授權 — 幾分鐘完成設置（支援 MSI）
  • 協同運作更順暢 — Office 應用間無縫提升生產力
  • 30 天全功能試用 — 無需註冊、無需信用卡
  • 最超值 — 一次購買，節省單獨外掛費用