Lookup
 

Can anyone tell me how to look up a value (A20) in a range (example A1:T15)
& return the value of the cell above & if possible the cell to the right.

Lookup
 

Is the value being looked up located in a specific column within (eg)
A1:T15, or can it be anywhere in that range?

You can use vlookup() to return cells to the right of the searched
value (as long as you're only looking in a specific column)

If looking across columns, you could try using a combination
of .Find() and .Offset()

Tim

On Nov 11, 2:39*pm, "Jim Lavery" wrote:
Can anyone tell me how to look up a value (A20) in a range (example A1:T15)
& return the value of the cell above & if possible the cell to the right.

Lookup
 

The value can be anywhere in the range in any column but would be unique. I
am familiar with vlookup but it only searches the first column.



"Tim Williams" wrote in message
...
Is the value being looked up located in a specific column within (eg)
A1:T15, or can it be anywhere in that range?

You can use vlookup() to return cells to the right of the searched
value (as long as you're only looking in a specific column)

If looking across columns, you could try using a combination
of .Find() and .Offset()

Tim

On Nov 11, 2:39 pm, "Jim Lavery" wrote:
Can anyone tell me how to look up a value (A20) in a range (example
A1:T15)
& return the value of the cell above & if possible the cell to the right.

Lookup
 

Jim
Try this macro..
Option Explicit
Sub Findvalue()
Dim findv As Long
Dim loc As Long
On Error Resume Next
findv = Range("A1:T15").Find(What:=Range("A20").Value, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
MsgBox (Selection.Cells.Offset(-1, 0)) & "/" & Selection.Cells.Offset(0, 1)
End Sub
Cimjet
"Jim Lavery" wrote in message
...
The value can be anywhere in the range in any column but would be unique. I am
familiar with vlookup but it only searches the first column.



"Tim Williams" wrote in message
...
Is the value being looked up located in a specific column within (eg)
A1:T15, or can it be anywhere in that range?

You can use vlookup() to return cells to the right of the searched
value (as long as you're only looking in a specific column)

If looking across columns, you could try using a combination
of .Find() and .Offset()

Tim

On Nov 11, 2:39 pm, "Jim Lavery" wrote:
Can anyone tell me how to look up a value (A20) in a range (example A1:T15)
& return the value of the cell above & if possible the cell to the right.

Lookup
 

typo.. Use this one
Option Explicit
Sub Findvalue()
On Error Resume Next
Range("A1:T15").Find(What:=Range("A20").Value, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
MsgBox (Selection.Cells.Offset(-1, 0)) & "/" & Selection.Cells.Offset(0, 1)
End Sub

"Cimjet" wrote in message
...
Jim
Try this macro..
Option Explicit
Sub Findvalue()
Dim findv As Long
Dim loc As Long
On Error Resume Next
findv = Range("A1:T15").Find(What:=Range("A20").Value, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
MsgBox (Selection.Cells.Offset(-1, 0)) & "/" & Selection.Cells.Offset(0, 1)
End Sub
Cimjet
"Jim Lavery" wrote in message
...
The value can be anywhere in the range in any column but would be unique. I
am familiar with vlookup but it only searches the first column.



"Tim Williams" wrote in message
...
Is the value being looked up located in a specific column within (eg)
A1:T15, or can it be anywhere in that range?

You can use vlookup() to return cells to the right of the searched
value (as long as you're only looking in a specific column)

If looking across columns, you could try using a combination
of .Find() and .Offset()

Tim

On Nov 11, 2:39 pm, "Jim Lavery" wrote:
Can anyone tell me how to look up a value (A20) in a range (example A1:T15)
& return the value of the cell above & if possible the cell to the right.

Lookup
 

A bit more clear..
Option Explicit
Sub Findvalue()
On Error Resume Next
Range("A1:T15").Find(What:=Range("A20").Value, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
MsgBox ("Above is " & Selection.Cells.Offset(-1, 0)) & " / " & "To the right
" & Selection.Cells.Offset(0, 1)
End Sub
Cimjet

"Cimjet" wrote in message
...
typo.. Use this one
Option Explicit
Sub Findvalue()
On Error Resume Next
Range("A1:T15").Find(What:=Range("A20").Value, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
MsgBox (Selection.Cells.Offset(-1, 0)) & "/" & Selection.Cells.Offset(0, 1)
End Sub

"Cimjet" wrote in message
...
Jim
Try this macro..
Option Explicit
Sub Findvalue()
Dim findv As Long
Dim loc As Long
On Error Resume Next
findv = Range("A1:T15").Find(What:=Range("A20").Value, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
MsgBox (Selection.Cells.Offset(-1, 0)) & "/" & Selection.Cells.Offset(0, 1)
End Sub
Cimjet
"Jim Lavery" wrote in message
...
The value can be anywhere in the range in any column but would be unique. I
am familiar with vlookup but it only searches the first column.



"Tim Williams" wrote in message
...
Is the value being looked up located in a specific column within (eg)
A1:T15, or can it be anywhere in that range?

You can use vlookup() to return cells to the right of the searched
value (as long as you're only looking in a specific column)

If looking across columns, you could try using a combination
of .Find() and .Offset()

Tim

On Nov 11, 2:39 pm, "Jim Lavery" wrote:
Can anyone tell me how to look up a value (A20) in a range (example A1:T15)
& return the value of the cell above & if possible the cell to the right.