提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中查找第一個,2nd或第n個匹配值?

假設您有兩列產品和數量如下圖所示。 為了快速找出第一或第二批香蕉的數量,你會怎麼做?

這裡的查找功能可以幫助你解決這個問題。 在本文中,我們將向您展示如何使用Excel中的Vlookup函數查找第一個,第二個或第n個匹配值。

Vlookup使用公式在Excel中找到第一個,2nd或第n個匹配值

使用Kutools for Excel輕鬆地在vlookup中找到Excel中的第一個匹配值


將多個工作表/工作簿合併到一個工作表/工作簿中:

將多個工作表或工作簿合併到一個工作表或工作簿中可能是您​​日常工作中的一項重大任務。 但是,如果你有 Kutools for Excel,其強大的實用性 - 結合 可以幫助您將多個工作表,工作簿快速合併到一個工作表或工作簿中。 立即下載Kutools for Excel的全功能60天免費試用版!

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 立即下載免費試用版!

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel - 最佳辦公生產力工具將解決您的大部分Excel問題
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文本; 將數字和貨幣轉換為英語單詞...
  • 合併工具:多個工作簿和表格合二為一; 合併多個單元格/行/列而不丟失數據; 合併重複行和總和...
  • 拆分工具:根據價值將數據拆分為多個表格; 一個工作簿到多個Excel,PDF或CSV文件; 一列到多列......
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 創建郵件列表和 通過Cell的價值發送電子郵件...
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 適用於Office 2007-2019和365; 支持所有語言; 在公司輕鬆部署; 全功能60天免費試用。

箭頭藍色右泡 Vlookup在Excel中找到第一個,2nd或第n個匹配值

請按照以下步驟查找Excel中的第一個2nd或第n個匹配值。

1。 在單元格D1中,輸入你想要查看的條件,在這裡我輸入香蕉。

2。 在這裡,我們將找到香蕉的第一個匹配值。 選擇一個空白單元格,如E2,複製和粘貼公式 =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) 進入配方欄,然後按 按Ctrl + 轉移 + 輸入 鍵同時。

注意:在此公式中,$ B $ 2:$ B $ 6是匹配值的範圍; $ A $ 2:$ A $ 6是包含所有vlookup條件的範圍; $ D $ 1是包含指定的查找條件的單元格。

然後您將在E2單元格中獲得香蕉的第一個匹配值。 使用此公式,您只能根據您的條件獲得第一個相應的值。

要獲得任何第n個相對值,可以應用以下公式: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + 按Ctrl + 轉移 + 輸入 鍵一起,這個公式將返回第一個匹配的值。

筆記:

1。 要找到第二個匹配值,請將上述公式更改為 =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)),然後按 按Ctrl + 轉移 + 輸入 鍵同時。 看截圖:

2。 上面公式中的最後一個數字表示vlookup條件的第n個匹配值。 如果將它更改為3,它將得到第三個匹配值,並更改為n,第n個匹配值將被查出。


箭頭藍色右泡 Vlookup通過Kutools for Excel在Excel中找到第一個匹配值

You可以很容易地在Excel中找到第一個匹配值,而不用記住公式 在列表中尋找一個值 配方公式 Kutools for Excel.

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

1。 選擇一個單元格來查找第一個匹配值(稱為單元格E2),然後單擊 Kutools > 配方助手 > 在列表中尋找一個值。 看截圖:

doc vlookup返回相鄰的下一個單元格010

2。 在裡面 配方助手 對話框:

1)。 在裡面 表格數組 框中,選擇包含第一個匹配值的表格。

2)。 指定包含您在查找中需要查找的條件的單元格(單元格D1) Look_value 框。

3)。 選擇具有第一個匹配值的列 框,最後點擊 OK 按鈕。

現在將根據下拉列表選擇在單元格C10中自動填充相應的單元格值。

提示。如果您想免費試用此實用程序,請轉到 免費下載軟件 首先,按照上述步驟進行操作。


Office Tab - 在Excel中選項卡式瀏覽,編輯和管理工作簿:

Office選項卡將Web瀏覽器(如Google Chrome,Internet Explorer新版本和Firefox)中顯示的選項卡界面帶到Microsoft Excel。 它將是一個節省時間的工具,在您的工作中無可比擬。 見下面的演示:

點擊免費試用Office Tab!

Excel的Office選項卡


Kutools for Excel - 最佳辦公生產力工具提高80%的生產力

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能60天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
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.
    Mark Rashleigh · 6 months ago
    If the first or any of the other entry's for 'banana' column B was blank cell, of which I don't require this number, what changes are required to this formula to skip blank cell in column B.
    • To post as a guest, your comment is unpublished.
      Mark Rashleigh · 6 months ago
      Sorry I am using this formula
      =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1))
      • To post as a guest, your comment is unpublished.
        Mark Rashleigh · 6 months ago
        SOLVED:
        =SMALL(IF(A2:A7=D1,IF(B2:B7<>"",B2:B7)),1)

        If 2nd or 3rd number required exchange ),1) to 2 or 3

        This formula does not require index, as it directly looks at the value in Cell
        • To post as a guest, your comment is unpublished.
          Mark Rashleigh · 6 months ago
          Correction to previous formula:
          The value was reading either the lesser or greater.

          Updated formula
          =INDEX($B$2:$B$7,SMALL(IF($A2:$A6=$D$1,IF($B$2:$B$7<>"",ROW($A2:$A6)-ROW($A2)+1)),1))

          This skips blank cell and places value of non blank cell. Replace +1 with +2 or +3 for 2nd or 3rd value
  • To post as a guest, your comment is unpublished.
    JAntonio · 8 months ago
    And if you want the last, second last, nth last just add a counter (count the number of events already hapenned) to the end and subtract it by 0,1,n respectively.

    Thank you so much! I was searching for this for a long time
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Good Day,
      Sorry can't help you with this yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Seth · 8 months ago
    Is it possible to find an average of the non-unique data. Or would it be possible to have a list dropdown on the cell of the various values?
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Good Day,
      Sorry can't help you with this yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Santosh · 9 months ago
    if a member was called on 1st October (October Data Set)as and it wasn’t revived, again the cce called him on 15th November(November Data Set). The member is revived on 16th November. While checking the revival using VLookup it makes YES to both the entries of October & November. How to avoid like it should show “YES” for the November entry actually when it was revived, and also leave the October entry as “NO”.
  • To post as a guest, your comment is unpublished.
    Sim Van Narith · 1 years ago
    Dear Ms/Mr,

    I have a problem:
    I knew a quantities of the product, I want to finding out the product name of the first or second match value of 200, what would you do?
    Big Thanks !

    Sim Van Narith
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Supposing the value of 200 locates in cell F2, please try this formula: =VLOOKUP(F2,IF({1,0},$B$2:$B$7,$A$2:$A$7),2,0).
      Hope it can help. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Matt · 1 years ago
    Is it possible to use this formula to find if a number is between two numbers. Below is my formula. Trying to see if a listing with individuals and an amount is between to other set cells (Example: $50,000 and $74,999)


    =ArrayFormula(INDEX('4 - Donors List'!$B$2:$B$1000,SMALL(IF('4 - Donors List'!$F$2:$F$1000>=D$2,ROW('4 - Donors List'!$F$2:$F$1000)-ROW('4 - Donors List'!$F$2)+1),$A6)))
  • To post as a guest, your comment is unpublished.
    R. G. · 1 years ago
    I have a little problem with this formula, it doesn't work exactly for my case:
    =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + Ctrl + Shift + Enter

    what if the criteria I am looking for is not exactly the same each time (Banana), but rather it becomes part of a phrase (banana republic) and so on; what then? By changing the "n" number at the end of this formula, I get "#NUM!" response. I have a column of vocabulary that I want to search for their meaning in the second column, and by typing one word, I need to get all occurrence of that word in any phrase to be listed. Any help on this?
    Thanks,
    R.G.
  • To post as a guest, your comment is unpublished.
    Christina · 1 years ago
    My current formula is {=IFERROR(INDEX(Sheet3!$C$2:$C$596,SMALL(IF(Sheet3!$A$2:$A$596=Sheet2!A19,ROW(Sheet3!$A$2:$A$596)-ROW(INDEX(Sheet3!$A$2:$A$596,1,1))+1),P19)),0)} But how would I apply this with multiple criteria, say two matches?
    • To post as a guest, your comment is unpublished.
      sunil · 9 months ago
      My problem is similar
      ping me if you find solution
      • To post as a guest, your comment is unpublished.
        Jones · 9 months ago
        create an auxiliary column wich concatenates your criterias, then use the concatenate as the criteria!

        Hope it works!
  • To post as a guest, your comment is unpublished.
    JB · 1 years ago
    Awesome tutorial! Worked like a charm, even across multiple sheets in the same file! Many thanks!!
  • To post as a guest, your comment is unpublished.
    Abby · 1 years ago
    Hi,
    I applied this formula but in my case I have numbers in place of the product name. When I drag down the formula to look for the next number in the list I get an error.

    events volume odd events only volume
    1 0.3 1 0.3
    1 2.5 1 2.5
    2 1.1 3 #NUM
    2 0.5 3 #NUM
    3 0
    3 0.2
    3 1
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Abby,
      The drag down feature can only work for same vlookup values. But in your case, the vlookup values are different (1 and 3).
      Please use this array formula: =IFERROR(INDEX($B$2:$B$8,SMALL(IF($C$4=$A$2:$A$8,ROW($A$2:$A$8)-ROW($A$2)+1),1+(ROW(A1)-1))), "") + Ctrol + Shift + Enter, and drag down the formula to get all matched values in the same vlookup value as below screenshot shown.
  • To post as a guest, your comment is unpublished.
    Warthogb · 2 years ago
    How can I increase last number automatically when dragging the formula down: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)),
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Warthogb,

      If you want to automatically get all matched values by dragging the formula down, please apply the following array formula:



      =IFERROR(INDEX($B$2:$B$7,SMALL(IF($D$1=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),1+(ROW(A1)-1))), "") + Ctrl + Shift + Enter
      • To post as a guest, your comment is unpublished.
        Warthogb · 2 years ago
        Crystal, Many many thanks, have only had a chance today 27/8/48 to see your help, will do formula later today :)
  • To post as a guest, your comment is unpublished.
    Ferdhy · 2 years ago
    =Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))
    • To post as a guest, your comment is unpublished.
      Nithin · 2 years ago
      [quote name="Ferdhy"]=Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))[/quote]
      i appreciate your help FERDHY. i tried out the formula , but since max(B2:B6) is 500 (orange), the value i get is 0.
      • To post as a guest, your comment is unpublished.
        Ferdhy · 2 years ago
        Hi,
        Just use this =SUMPRODUCT(MAX(((A2:A8)=D1)*(B2:B8)))
        once you change in D1 and put Banana, you should get 300 , if you put Orange, you will get 500.

        Ferdhy
        • To post as a guest, your comment is unpublished.
          Rita · 2 years ago
          You can also use:
          =max(if(A2:A6=D1, B2:B6)) + Ctrl + Shift + Enter
  • To post as a guest, your comment is unpublished.
    Nithin · 2 years ago
    How to find the maximum value of Bananaa
  • To post as a guest, your comment is unpublished.
    Nithin · 2 years ago
    Help me to find the maximum value of Bananaa using a formula . Ie to display 300
  • To post as a guest, your comment is unpublished.
    Dynes · 2 years ago
    If the value result is #NUM! could you please show me the formula to add so it will return to ZERO result.

    Thank you
    • To post as a guest, your comment is unpublished.
      alan · 2 years ago
      Just add IFERROR(your formula, the result you want to return), for example, the formula is =sum(A1:A6), then it would convert to =IFERROR(sum(A1:A6),""), it will return blank if the result is error like #NUM!.
  • To post as a guest, your comment is unpublished.
    MT · 2 years ago
    What if the banana can be yellow or green, how can we use this formula to show the right quantity based on two values (instead of one just now)? Thanks for your help!
  • To post as a guest, your comment is unpublished.
    Danish · 3 years ago
    Thank you so much!!!
  • To post as a guest, your comment is unpublished.
    EKTA SINGH · 3 years ago
    Hi

    Can you provide the video for the above mentioned formula to fatch the 2nd ,3rd value of the data