Odd Lookup Formula

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi. Can I write a formula that will scan all fo Sheet2, with a lookup
value in cell Sheet1!A1, and return the value in the cell that is 8
rows directly below the cell where the lookup value is found?

Thanks!
 
=offset(sheet2!$C$1,Match(Sheet1!$A$1,Sheet2!$C:$C,0)-1,0)

would give you the found cell

=offset(sheet2!$C$1,Match(Sheet1!$A$1,Sheet2!$C:$C,0)+7,0)
would be 8 cells below (depending on how you count the 8)

You could also use Index
 
All of Sheet2? Or just a certain row in Sheet2? I don't believe you
can use a formula to search a entire worksheet for a value. If you
are searching a particular row, you can use the HLookup function to
get a value that is 8 rows down from teh found value.
 
Right, I know I how to use the HLookup. But I need to search an
entire sheet (ar a large array), but NOT a single row or single
column!!
 
Thanks Tom. Question - this works great if what I am looking for is
in column C. But I have no idea what column it may be in! Is there a
way to scan multiple columns and rows? For instance, my array would
be A1:Z5000. My lookup value can be anywhere in that array. Thanks
so much!!
 
Steve,

Could have sworn I read "particular column", but I guess not. Nothing
immediately comes to mind. It sounds like you want a formula solution, so to
increase your odds you should ask in Worksheet.Functions or Misc.
 
Thanks Tom. I thought a formula solution would be
easiest...apparently not! Anyway, it doesn't need to be a formula
solution, so can it be done with code?
 
here's a convoluted way using vb to find your criteria and then creating a
formula in the cell. i put the formula in C1 in this example. i tried to break
the formula line so it wouldn't wrap, if there's a problem, just put it all on 1
line.

Sub test()
Dim rngfound As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

With ws2.Cells
Set rngfound = .Find(ws1.Range("A1"), LookIn:=xlValues, lookat:=xlWhole)
If Not rngfound Is Nothing Then
ws1.Range("c1").Formula = "=hlookup(A1" & ",Sheet2!" & _
Range(Cells(rngfound.Row, rngfound.Column).Address, _
Cells(rngfound.Row + 8, rngfound.Column)).Address & ",9,False)"
End If
End With
End Sub
 
Back
Top