在单元格范围内查找值，并将值返回一定距离 [英] Find value within a range of cells and return a value a certain distance away

问题描述

我有一系列带有产品代码字段的页面，我从我的网站复制（原始源文件已被错位）。我现在有excel中的页面数据，需要使用公式找到Product Code标题，然后返回该标题下的值。虽然标题在页面之间是一致的，但每页的页面上的值在哪里是有点不同。

I have a series of pages with "product code" fields that I am copying from my website (the original source document has been misplaced). I now have the page data in excel and need to use a formula to find the heading "Product Code" and then return the value that is under that heading. Though the headings are consistent between pages, each page is a bit different in terms of where on the page that value is.

我需要制定最好的公式，以便无论产品代码在哪个单元格中，excel都可以找到它，然后给它下面的单元格的值。

I need to work out the best formula so that regardless of which cell the "Product code" is in, excel can find it and then give the value of the cell below it.

看起来很直接，但我只是可以我不知道该怎么做，我已经在别的地方看过，似乎没有改变别人使用VLOOKUP，HLOOKUP，MATCH& INDEX公式。

It seems straight forward but I just can't get it and it's doing my head in. I have looked elsewhere and can't seem to rework other people's examples that use VLOOKUP, HLOOKUP, MATCH & INDEX formulas.

有谁知道这是可能的吗？

Does anyone know how this would be possible?

推荐答案

p>到目前为止，最简单和最灵活的方法是使用VBA函数（您可以简单地使用它，因为它是一个正常的工作表函数）。这是VBA

By far the easiest and most flexible way is to use a VBA function (that you can simply use as it were a normal worksheet function). Here is the VBA

Public Function Find_Pcode(ByRef to_search As Range, Findthat As String) As Variant
    Find_Pcode = to_search.Find(what:=Findthat, MatchCase:=False).Offset(1, 0).Value
End Function

如果您真的想/不得不使用内置的工作表函数，那么如果您不知道标签产品代码中将包含哪些列或行，那么这是一个非常有用的方法。

If you really want to/have to use built-in worksheet functions, it is a bit fiddly if you do not know what columns or rows your label "Product Code" will be in.

如果你知道ROW，那么可以这样做：

If you do know the ROW, for instance, then it can be done like this:

= OFFSET（G5,1，MATCH（产品代码，G5：O5,0）-1）

其中 G5 是 G5：O5 中的第一个单元格，您知道它包含具有产品代码的单元格。正如我所提到的，只有当你知道要查看哪一行时，这才有效。

where G5 is the first cell in the row G5:O5 which you know contains the cell with "Product Code" in it. As I mentioned, this only works if you know which row to look at.

另外，如果你知道它总是在列A中，那么你可以还使用这个工作表函数

Also, if you know it will always be in Column A, for instance, then you can also use this worksheet function

= OFFSET（A1，MATCH（Product Code，A1：A25,0），0）

其中 A1：A25 是要搜索包含产品代码

Where A1:A25 is where you want to search for the cell containing "Product Code".

下面的图片详细介绍了如何使用这些功能（可能需要放大！）

The pictures below detail how to use the functions (might have to zoom in!)

要使用VBA。打开VBA窗口 - >在工作表中插入Module（通过左窗格右键单击工作表名称） - >将VBA粘贴到模块中 - >使用VBA，如图2所示。

To use the VBA. Open VBA window -> insert "Module" in your worksheet (via left pane and right click on worksheet name) -> paste the VBA into the module -> use VBA like in picture 2.

VBA更灵活，您可以给它一个平方/任意大小的搜索范围。

The VBA is more flexible, as you can give it a square/any-size range to search.

这篇关于在单元格范围内查找值，并将值返回一定距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！