Excel INDEX/MATCH，但列并不总是位于同一位置 [英] Excel INDEX/MATCH but the columns aren't always in the same place

问题描述

我在电子表格中收到的数据并不总是列在同一位置.我发现一些代码可以根据VBA中的数组对列进行重新排序，但是每次在它们周围移动时，所有引用都会移动，就像我想引用第1列，但我将第1列放入第3列，并将所有公式放在工作表中一样然后将其更改为第3列.我想您可以在我的历史记录中找到关于这个问题的问题.

所以现在我有一个包含很多列的表，其中两列是 one_unknown 和 two_unknown ，它们可以在工作表中的任何位置.我想在 one_unknown 列中找到 x 的值，该值与中的值 y 相同two_unknown 列.我最终从INDEX和MATCH到一堆纠结的公式，从

开始

  MATCH("two_unknown"，1:1) 

获得 two_unknown 的列号，以及获取 one_unknown 的列号的相同过程.

但是然后我想使用INDEX并告诉它在 two_unknown 中查找 y ，但是我所拥有的只是一个数字，而不是一个范围，说它是否在列中4我有一个LONG变量4，我想要一个RANGE变量D:D.

我开始编写一个函数，将数值转换为该列的数字所定义的范围，这看起来太愚蠢了.我想到的所有其他解决方案都一样优雅而残缺不全，当解决方案可能比我想象的简单得多时，我觉得我盯着它看了太久了.

有人知道我应该怎么做吗?

预先感谢

解决方案

这可以通过许多公式方法来解决.

如何将数据转换为Excel表?确保列名是您所期望的.在此公式中，查找值位于名为LookHere的列中，而结果列名为ReturnThis，则可以使用

  = INDEX(Table1 [ReturnThis]，MATCH(I2，Table1 [LookHere]，0)) 

或者，如果您拥有Office 365，请使用新的XLookup

  = XLOOKUP(K2，Table1 [LookHere]，Table1 [ReturnThis]) 

如果由于某种原因而无法将数据转换为Excel表，则可以使用MATCH返回列的位置，例如，假设列标签位于第1行:

  = MATCH("LookHere"，1:1,0)= MATCH("ReturnThis"，1:1,0) 

将它们放入工作表单元格(如单元格K4和K5)，然后使用这样的Index/Match，使用将两个INDEX公式转换为带交点:"的范围来构建索引.操作员.您需要为此方法的第一行和最后一行提供行号.

  = INDEX(INDEX(A:F，1，K5):INDEX(A:F，100，K5)，MATCH(K2，INDEX(A:F，1，K4):INDEX(A:F，100，K4)，0)) 

OR

使用这些公式设置命名范围并使用命名范围= INDEX(INDEX(A:F，1，ReturnThisColumn):INDEX(A:F，100，ReturnThisColumn)，MATCH(K2，INDEX(A:F，1，LookHereColumn):INDEX(A:F，100，LookHereColumn)，0))

OR

如果拥有新的Let()函数，则可以使用它代替命名范围.

I've got data coming to me in spreadsheets where the columns aren't always in the same place. I found some code to reorder the columns according to an array in VBA but every time I shift them around it moves all the references, like I want to refer to column 1 but I put column 1 in column 3 and all the formulas in the worksheet followed that change to column 3. I think you can find my question about that in my history.

So now I have a table with lots of columns, and two of those columns are one_unknown and two_unknown, and they could be anywhere on the sheet. I want to find the value of x which is in the one_unknown column, that has the same row number as value y in the two_unknown column. I end up with a bunch of tangled formulas with INDEX and MATCH, starting with

MATCH("two_unknown",1:1)

to get the column number of two_unknown, and the same process to get the column number of one_unknown.

But then I want to use INDEX and tell it to look for y in two_unknown but all I have is a number, not a range, say if it's in column 4 I have a LONG variable 4 and I want a RANGE variable D:D.

I started writing a function to turn a numeric value into the range defined by that column's number and it just seemed so silly. Every other solution I think of is just as inelegant and mangled and I feel like I've been staring at it too long when the solution is probably way simpler than I think.

Anyone know what I should do?

Thanks in advance

解决方案

This can be solved with a number of formula approaches.

How about turning the data into an Excel Table? Ensure the names of the columns are what you expect. In this formula, the lookup value is in a column called LookHere and the result column is called ReturnThis, then you can use

=INDEX(Table1[ReturnThis],MATCH(I2,Table1[LookHere],0))

Or if you have Office 365, use the new XLookup

=XLOOKUP(K2,Table1[LookHere],Table1[ReturnThis])

If, for some reason, you can't turn the data into an Excel table, you can use MATCH to return the position of the columns for example like this, assuming the column labels are in row 1:

=MATCH("LookHere",1:1,0)
=MATCH("ReturnThis",1:1,0)

Put these into worksheet cells, like cells K4 and K5 and then use an Index / Match like this, building an index with two INDEX formulas turned into a range with the intersection ":" operator. You will need to provide row numbers for first and last row for this approach.

=INDEX(INDEX(A:F,1,K5):INDEX(A:F,100,K5),MATCH(K2,INDEX(A:F,1,K4):INDEX(A:F,100,K4),0))

OR

set up named ranges with these formulas and use the named ranges =INDEX(INDEX(A:F,1,ReturnThisColumn):INDEX(A:F,100,ReturnThisColumn),MATCH(K2,INDEX(A:F,1,LookHereColumn):INDEX(A:F,100,LookHereColumn),0))

OR

If you have the new Let() function, you can use that instead of named ranges.

这篇关于Excel INDEX/MATCH，但列并不总是位于同一位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！