如左圖所示，您需要在表中查找並列出值“ Linda”的所有匹配實例。 如何實現呢？ 請嘗試本文中的方法。
1.選擇一個空白單元格以輸出第一個匹配的實例，在其中輸入以下公式，然後按 按Ctrl + 轉移 + Enter 鍵同時。
您可以輕鬆找到並列出值的第一個匹配實例。 在列表中查找值 功能 Kutools for Excel 不記得公式。 請執行以下操作。
申請前 Kutools for Excel請 首先下載並安裝.
1.選擇一個空白單元格，您將放置第一個匹配的實例，然後單擊 Kutools > 公式助手 > 公式助手.
2。 在裡面 公式助手 對話框，您需要：
保養竅門：您可以檢查 篩選 框，在文本框中輸入關鍵字以快速過濾所需的公式。
保養竅門：列號基於所選的列數，如果您選擇四列，而這是第三列，則需要在列號中輸入數字3。 柱 框。
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.· 4 months agoQuestion, I have a sheet laid out opposite of this where "Linda" would be the column header I would like to flag on and the value "90" or 89" I would like to return. Would I change the row function to a column function?
To post as a guest, your comment is unpublished.· 5 months agoHow would I use this formula but instead of filling down I could fill across but the formula would continue moving down as I fill across (ie. the formula changes from ROW(1:1) to ROW(2:2)
To post as a guest, your comment is unpublished.· 5 months agoHi Luke,The below array formula can do you a favor.Note: After entering the formula, please press the Ctrl + Shift + Enter key to apply it. And then drag its Fill Handle right across the cells you need.=INDEX($B$2:$B$11, SMALL(IF($D$2=$A$2:$A$11, ROW($A$2:$A$11)-ROW($A$2)+1), COLUMN(A1)))
To post as a guest, your comment is unpublished.· 1 years agoA very useful and educational formula. I am matching against a set numeric value and it works a treat. How can I use >= rather than just = (i.e. IF($D$2>=$A$2:$A$11) to perform the match so it is looking for values greater than a set value (in $D$2). I assume as it doesn't work as one of the functions in the formula is matching against specific text rather than working with numbers?
To post as a guest, your comment is unpublished.· 2 years agoThis was super useful, thanks! I'm trying to take this one step further and be able to return all match instances of a certain value while having to search through more than a single-column array. To work through this using your example, I added a second column of test scores and modified your formula to look up a given test score and return the names that match that score. I got this to work with INDEX(SMALL()) and can pull all of the names from both test columns. I've also managed to return only names with that score on Test 2 using INDEX(MATCH(INDEX(MATCH))), however this can only find the first instance in the array. What I'm really trying to do is a combination of these: return all of the names with the given array, while narrowing the search to a specific column within the array. Do you have any tips for this?
To post as a guest, your comment is unpublished.· 1 years agoGood day,
Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do? Thank you for your comment.
To post as a guest, your comment is unpublished.· 3 years agoIs there a similar formula that will work in google sheets?