excel在多个列中查找 [英] excel lookup within multiple columns

问题描述

我希望有人可以帮助我完成一个简单的任务.

I hope someone can help with what I thought was a simple task.

我有一个包含许多列的工作表(Sheet1).前两个分别是一个数字和一个名称.在这两列之后，每一行都有许多列(例如，第一行有10个列，第二行有4列，等等).

I have a worksheet(Sheet1) that has numerous columns. The first two are just a number and a name respectively. After these two columns there are a number of columns which is different for each row(e.g. row one has 10 colums row 2 has 4 columns etc).

我有另一个包含2列的工作表(Sheet2).第一列具有我要在Sheet1中查找的值.问题是该值可以在前两列之后的Sheet2的列中的任何位置.(例如，查找值123，值在第13列第6行或查找值456在第5列第14行)，然后我希望在将Sheet1上的第1列第6行放入Sheet2上的第2列.

I have another worksheet(Sheet2) with 2 columns. The first column has a value that I want to lookup within Sheet1. The problem is that the value can be anywhere within the columns on Sheet2 after the first two columns.(e.g. lookup value 123, value is in column 13 row 6 or lookup value 456 is in column 5 row 14) I then want the value in the column 1 row 6 on Sheet1 to be put into column 2 on Sheet2.

我尝试使用Vlookup，Hlookup和Lookup，但似乎无法弄清楚如何使其工作.

I have tried using Vlookup,Hlookup and Lookup but cant seem to figure out how to get it to work.

任何帮助将不胜感激

谢谢加雷斯(Gareth)

Thanks Gareth

推荐答案

...我知道您并没有要求VBA解决方案，但是我只是想写一个，看看我会怎么做...

... I know you didn't ask for a VBA solution, but I just felt like writig one up to see how I would do it...

我使它快速又脏"，没有进行任何错误检查等，但是它将为您提供所需的内容:

I made it "quick and dirty" without ANY error checking etc, but it will give you what you're looking for:

  Public Function FindInRange(Val As Variant, LookIn As Range, ColIndexNumber As Integer) As Range

  ' INPUTS:
     ' Val             = The Value you're looking for in your Range of cells
     ' Range           = The range of cells you're searching through
     ' ColIndexNumber  = The index of the column you want a value returned from within the row from which the value is found

  ' NOTE:
  ' This will only pull the first value matching your "Val" argument

  Dim FoundCell As Range

  Set FoundCell = LookIn.Find(what:=Val, LookAt:=xlWhole)

  If FoundCell Is Nothing Then
     Set FindInRange = Nothing
  Else
     Set FindInRange = Intersect(LookIn.Columns(ColIndexNumber), FoundCell.EntireRow)
  End If

  End Function

---

如果将其粘贴到VBA模块中，则可以像此时的其他任何函数一样从电子表格中调用它.

---

If you paste this into a VBA module, you can call it from your spreadsheet like any other function at that point.

希望这会有所帮助:)

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