如何通过公式提取非空单元格（含34、28）对应的akhil和sam？

Got it, let's tackle this problem where you need to pull the associated names (akhil and sam) for the non-empty cells containing 34 and 28. Here are a couple of solid approaches depending on your Excel version:

If you're using the latest Excel version with dynamic array support, this formula will automatically spill the results into separate cells without needing to drag it down:

=FILTER(B:B, A:A<>"")

(Assuming your numeric values (34, 28) are in Column A, and the corresponding names (akhil, sam) are in Column B.)

The FILTER function checks for non-empty cells in Column A and returns the matching names from Column B directly—super clean and efficient.

If you don't have dynamic array features, you can use TEXTJOIN to combine the names into a single cell (with a delimiter like a comma):

=TEXTJOIN(", ", TRUE, IF(A:A<>"", B:B, ""))

• Important note: For pre-365 Excel, this is an array formula—you need to press Ctrl + Shift + Enter instead of just Enter after typing it to make it work.
• The TRUE argument tells TEXTJOIN to skip empty entries, and the IF statement only pulls names where the corresponding value cell isn't empty.

If you specifically want to target cells with exactly 34 or 28 (not just any non-empty cell), tweak the formulas like this:

• Dynamic Array version:

  =FILTER(B:B, (A:A=34)+(A:A=28))
  

• Older Excel version:

  =TEXTJOIN(", ", TRUE, IF((A:A=34)+(A:A=28), B:B, ""))
  

The +(A:A=34)+(A:A=28) acts as an OR condition here, so it picks up cells that match either value.

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