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

如何将数据转换为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.