Excel技术问题：搜索指定区域字符串后如何返回所在行数据？

Got it! Since you’ve already confirmed your target string exists in the range using =COUNTIF(rng,"*"&value&"*")>0, let’s walk through how to pull the corresponding data from those matching rows. Here are the most reliable methods depending on your Excel version and needs:

• INDEX + MATCH (Works with All Excel Versions)
  This is the go-to flexible solution—works no matter if your target data is to the left or right of the matching column. Let’s define the terms first:
  • rng: The range you’re checking for the target string
  • data_range: The range holding the corresponding data you want to extract
  • value: Your target string

Use this formula:

=INDEX(data_range, MATCH("*"&value&"*", rng, 0))

How it works: MATCH locates the position of the first cell in rng that contains your value (the wildcards * let it match partial strings), then INDEX grabs the matching row’s data from data_range.

• XLOOKUP (Excel 365/2021 or Later)
  If you’re on a newer Excel version, XLOOKUP simplifies this with cleaner syntax and built-in wildcard support:

=XLOOKUP("*"&value&"*", rng, data_range, "No match found")

The fourth parameter ("No match found") is optional—replace it with whatever text you want to display if there’s no match (though you already know a match exists from your COUNTIF check!).

• Extract All Matching Results (For Multiple Rows)
  If multiple rows contain your target string and you want to pull all corresponding data into one cell, use TEXTJOIN (Excel 365/2021+) paired with an IF statement:

=TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH(value, rng)), data_range, ""))

• TEXTJOIN combines all matching results using ", " as a separator—feel free to change this to any delimiter you prefer
• SEARCH checks for the value in each cell of rng (it’s case-insensitive; use FIND instead if you need case-sensitive matching)
• Note: In older Excel versions, this is an array formula—you’ll need to enter it with Ctrl+Shift+Enter instead of just pressing Enter.

Quick Tips #

• Double-check that rng and data_range have the same number of rows to keep row alignment correct.
• If your value includes wildcard characters like * or ?, escape them with a tilde (~) to avoid false matches. For example: "*"&SUBSTITUTE(value, "*", "~*")&"*".

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