如何实现列中多值查找：匹配所有"V"并返回对应行内容？

I totally get your frustration—LOOKUP is great for single-value matches, but it falls flat when you need to pull every name (like Pete or James) linked to "V" in your target column. Here are three reliable methods to solve this, tailored to different Excel versions:

1. Use the FILTER Function (Excel 365/2021+) #

This is the simplest solution if you have a modern Excel version. Assume your "V" values are in Column A, and the corresponding names are in Column B. Just drop this formula into a blank cell:

=FILTER(B:B, A:A="V", "No matches found")

• The first argument (B:B) tells Excel which column to pull results from (your names column).
• The second argument (A:A="V") sets the match condition.
• The third optional argument lets you specify what to show if there are no matches (feel free to change this to "" to show a blank instead).

2. Use INDEX + SMALL + IF (Older Excel Versions) #

If you're stuck with an Excel version that doesn't support FILTER, this array formula will do the trick. Let's say your data starts at Row 2 (Row 1 is headers):

=IFERROR(INDEX($B$2:$B$100, SMALL(IF($A$2:$A$100="V", ROW($A$2:$A$100)-ROW($A$2)+1), ROWS($C$2:C2))), "")

Here's how to use it:

• Paste the formula into a blank cell (say, C2).
• Press Ctrl+Shift+Enter (this is required for array formulas in older Excel).
• Drag the fill handle down until you see blank cells—those blanks mean you've pulled all matching names.

Quick breakdown: #

• IF($A$2:$A$100="V", ROW(...)...) identifies the relative row numbers of all cells in Column A that equal "V".
• SMALL(..., ROWS($C$2:C2)) grabs the 1st, 2nd, 3rd, etc., matching row number as you drag the formula down.
• INDEX pulls the corresponding name from Column B using those row numbers.
• IFERROR ensures you get a blank instead of an error once there are no more matches.

3. Use Power Query (All Excel Versions) #

For larger datasets or if you need to repeat this task regularly, Power Query is a robust, non-formula option:

• Select your entire data range, then go to the Data tab > From Table/Range (in older Excel, look for "Get & Transform Data" tools).
• In the Power Query Editor, click the filter arrow on Column A's header, then only check the "V" option.
• If you only need the names column, right-click Column A's header and select Remove.
• Click Close & Load to export the filtered list of names back to your worksheet.

Any of these methods should get you all the Pete and James entries linked to "V" in your column!

内容的提问来源于stack exchange，提问作者Gary Polley