Excel在多列数组中查找，返回行 [英] Excel lookup in multiple column array, return row

问题描述

我需要在表中查找某物的值，然后返回它所在的行.该值可以在任何列中，因此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)

这是一个数组公式，因此您必须通过 Ctrl + Shift + Enter

It's an array formula so you have to confirm it with Ctrl + Shift + Enter

这篇关于Excel在多列数组中查找，返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！