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
--
Regards,
Tom Ogilvy

"Steve" wrote:

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!

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.
Steve wrote:
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!

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

On Sep 19, 11:52 am, JW wrote:
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.



Steve wrote:
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!- Hide quoted text -

- Show quoted text -

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

On Sep 19, 11:52 am, Tom Ogilvy
wrote:
=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
--
Regards,
Tom Ogilvy



"Steve" wrote:
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!- Hide quoted text -

- Show quoted text -

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.

--
Regards,
Tom Ogilvy

"Steve" wrote:

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

On Sep 19, 11:52 am, Tom Ogilvy
wrote:
=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
--
Regards,
Tom Ogilvy



"Steve" wrote:
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!- Hide quoted text -

- Show quoted text -

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?

On Sep 19, 12:48 pm, Tom Ogilvy
wrote:
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.

--
Regards,
Tom Ogilvy



"Steve" wrote:
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!!

On Sep 19, 11:52 am, Tom Ogilvy
wrote:
=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
--
Regards,
Tom Ogilvy

"Steve" wrote:
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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

--


Gary


"Steve" wrote in message
ps.com...
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?

On Sep 19, 12:48 pm, Tom Ogilvy
wrote:
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.

--
Regards,
Tom Ogilvy



"Steve" wrote:
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!!

On Sep 19, 11:52 am, Tom Ogilvy
wrote:
=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
--
Regards,
Tom Ogilvy

"Steve" wrote:
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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -