Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vertical lookup with a lookup value that returns multiple matches | Excel Programming | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Lookup Ques - finding value within a string to lookup | Excel Programming |