excel lookup within multiple columns

Question

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

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).

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.

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

Any help would be much appreciated

Thanks Gareth

Answer 1

... 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

---

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

Hope this helps :)