I have a list of numbers in a table that I would like to search for and bring back the cell reference of where that number resides. For example the data looks like:
我有一个表格中的数字列表,我想搜索并带回该数字所在位置的单元格引用。例如,数据看起来像:
A B C D
1 1 2 3 4
ok it doesn't come out very well as the first one is the row number and then each number below sits under each letter, so C1 would contain '3'....
确定它不会很好,因为第一个是行号,然后下面的每个数字都在每个字母下面,所以C1将包含'3'....
If I wanted to return the reference number of C1 in a cell I am using the formula of =CELL("address",MATCH(AU14,C1:AG1)) but this just errors. I have tried to put an Index in there too, but I believe that index only works vertically so this bring back a #N/A result.
如果我想在单元格中返回C1的引用号,我使用公式= CELL(“address”,MATCH(AU14,C1:AG1)),但这只是错误。我也试过把一个索引放在那里,但我相信索引只能垂直工作,所以这会带来一个#N / A结果。
Can anyone assist as I've wasted too much time on this already! :)
任何人都可以协助,因为我已经浪费了太多时间! :)
2 个解决方案
#1
To make CELL work you need a cell reference, e.g.
要使CELL工作,您需要一个单元格引用,例如
CELL("address",C1)
The trouble is that MATCH just gives you a number, not a cell reference.
问题是MATCH只给你一个数字,而不是一个单元格引用。
Probably the easiest way is to use the ADDRESS function, so a first try might be
可能最简单的方法是使用ADDRESS函数,因此可能是第一次尝试
=ADDRESS(1,MATCH(AU14,C1:AG1,0)+2)
That would give you the right answer if AU14 contained 3, but isn't considered to be very good because it wouldn't update if you deleted/inserted rows or columns.
如果AU14包含3,那将为您提供正确答案,但不被认为是非常好的,因为如果删除/插入行或列,它将不会更新。
A better one would be
一个更好的是
=ADDRESS(ROW(C1),MATCH(AU14,C1:AG1,0)+COLUMN(C1)-1)
Then you might want to put in some error handling for the case where it's not found
然后,您可能希望对未找到它的情况进行一些错误处理
=IFERROR(ADDRESS(ROW(C1),MATCH(AU14,C1:AG1,0)+COLUMN(C1)-1),"Not found")
#2
You are indeed missing an INDEX. And INDEX works vertically, horizontally, or both depending on how it's called.
你确实错过了一个INDEX。 INDEX可以垂直,水平或两者工作,具体取决于它的调用方式。
Here is a formula that works for the ranges in the pictures. Should be easy to modify.
这是一个适用于图片范围的公式。应该很容易修改。
Formula in C5
C5中的公式
=CELL("address",INDEX(B2:F2,MATCH(C4,B2:F2,0)))
formula
results
#1
To make CELL work you need a cell reference, e.g.
要使CELL工作,您需要一个单元格引用,例如
CELL("address",C1)
The trouble is that MATCH just gives you a number, not a cell reference.
问题是MATCH只给你一个数字,而不是一个单元格引用。
Probably the easiest way is to use the ADDRESS function, so a first try might be
可能最简单的方法是使用ADDRESS函数,因此可能是第一次尝试
=ADDRESS(1,MATCH(AU14,C1:AG1,0)+2)
That would give you the right answer if AU14 contained 3, but isn't considered to be very good because it wouldn't update if you deleted/inserted rows or columns.
如果AU14包含3,那将为您提供正确答案,但不被认为是非常好的,因为如果删除/插入行或列,它将不会更新。
A better one would be
一个更好的是
=ADDRESS(ROW(C1),MATCH(AU14,C1:AG1,0)+COLUMN(C1)-1)
Then you might want to put in some error handling for the case where it's not found
然后,您可能希望对未找到它的情况进行一些错误处理
=IFERROR(ADDRESS(ROW(C1),MATCH(AU14,C1:AG1,0)+COLUMN(C1)-1),"Not found")
#2
You are indeed missing an INDEX. And INDEX works vertically, horizontally, or both depending on how it's called.
你确实错过了一个INDEX。 INDEX可以垂直,水平或两者工作,具体取决于它的调用方式。
Here is a formula that works for the ranges in the pictures. Should be easy to modify.
这是一个适用于图片范围的公式。应该很容易修改。
Formula in C5
C5中的公式
=CELL("address",INDEX(B2:F2,MATCH(C4,B2:F2,0)))
formula
results