Lookup

[Jim Lavery]

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.

 

[Tim Williams]

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

 

[Jim Lavery]

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.



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

 

[Tim Williams]

You can use this user-defined function: paste into a regular module

'***********************************************************************
Function FindIt(SearchRange, ValToFind)
Dim f As Range, rv
rv = ""
Set f = SearchRange.Find(ValToFind, , xlValues, xlWhole)
If Not f Is Nothing Then
If f.Row > 1 Then
rv = f.Offset(-1, 0).Value 'cell above
'rv = f.Offset(0, 1).Value 'cell to right
End If
End If
FindIt = rv
End Function
'***********************************************************************

Tim

 

[Cimjet]

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

 

[Cimjet]

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]

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