问题描述
我需要在表中查找某物的值，然后返回它所在的行.该值可以在任何列中，因此Match似乎并不理想.最好的方法是什么?
I need to lookup the value of something in a table and then return the row that it's in. The value can be in any column, so Match doesn't seem ideal. What's the best way to do this?
例如，假设该表有2列.第1列包含A，B，C，D.第2列包含E，F，G，H.我想找出"G"所在的行，因此我想以某种方式返回"3"而无需事先知道"G" 位于第2列中.
As an example, say the table has 2 columns. Column 1 has A, B, C, D. Column 2 has E, F, G, H. I want to find out which row "G" is in, so I want to somehow return "3" without knowing beforehand that "G" is in column 2.
推荐答案
在这种布局下，假设您的数据不重复:
Assuming your data isn't duplicated, given this layout:
Column 1 Column 2
A E
B F
C G
D H
，此公式:
=MAX(IF(A1:B5="G",ROW(A1:B5),0))
将做您想要的.在这种情况下，它将返回4.它还具有处理无限数量的列的能力(如果没有重复，那么很多列也可能会影响性能)
will do what you want. In this case it would return 4. It also has the ability to work with an infinte number of columns (if nothing is duplicated, also a lot of columns might impact performance)
这是一个数组公式，因此您必须通过 + +
It's an array formula so you have to confirm it with + +
这篇关于Excel在多列数组中查找，返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！