Using offset with vlookup
Closed
Mike
-
Jul 28, 2009 at 02:33 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 29, 2009 at 08:24 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 29, 2009 at 08:24 PM
Related:
- Using offset with vlookup
- Mouse pointer is offset - Guide
- Transfer data from one excel worksheet to another automatically vlookup - Guide
- Vlookup to find missing data in 2 columns - Excel Forum
- Removing "FALSE" from an IF statement with a VLOOKUP ✓ - Excel Forum
- Vlookup if two cells match return value from third ✓ - Excel Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 28, 2009 at 08:49 PM
Jul 28, 2009 at 08:49 PM
I think vlookup and offset only picks up only the value and not the cell address.you can have a macro. post a small extract of sheet and sheet 2 , if necessary, with fictitious data
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 29, 2009 at 08:24 PM
Jul 29, 2009 at 08:24 PM
Ok I am going to give you a small macaro. I am sure you know how to run the macro.
in the macro there are some message boxes This is for you to check whether the cell ADDRESSES are what your require and if there is a small error you can adjust the previous code statement in the offset function.
The final sum you require will be in the final message box.
POST FEEDBACK.
the macro is
in the macro there are some message boxes This is for you to check whether the cell ADDRESSES are what your require and if there is a small error you can adjust the previous code statement in the offset function.
The final sum you require will be in the final message box.
POST FEEDBACK.
the macro is
Sub test()
Dim rng1 As Range, x As Double, cfind As Range
Dim rng2 As Range, rngsum As Range, y As Double
On Error Resume Next
With Worksheets("sheet1")
Set rng1 = .Range("c8")
x = rng1.Value
End With
With Worksheets("sheet2")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
MsgBox "this value is not available. exiting macro"
GoTo line1
End If
MsgBox cfind.Address
Set rng2 = cfind.Offset(3, 12)
MsgBox rng2.Address
Set rngsum = Range(rng2, rng2.Offset(3, 18))
MsgBox rngsum.Address
y = WorksheetFunction.Sum(rngsum)
MsgBox y
End With
line1:
End Sub
Jul 29, 2009 at 08:17 AM
I was able to identify one of the formulas - the one that retrieves a single cell from Sheet 2 (3 rows down and 12 columns to the right) of the cell that matches C8 in Sheet 1. A victory for me to be sure. It may not be the most efficient, but it works. If there is a better way, I'd love to hear of it.
=INDEX(Sheet2!$F$3:$Z$300,MATCH(C8,Sheet2!$F$3:$F$300,0)+3,12)
Part 2 of my question is still a mystery to me. Given all the same ranges and references as identified in the formula above, how would I retrieve the sum of a range of cells? For example, starting with the cell that the above formula returns, how would I sum from there (3 rows down and 12 columns to the right of the "Match") to 3 rows down and 18 columns to the right of the "Match"?
I hope this is clear, and very much appreciate any help you can provide.