Using offset with vlookup

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
I am relatively new with macros, and would appreciate some help with a formula. Sheet 1 has my property names (in column A) and I wish to pull data into column B relating to those properties from sheet 2 . Can you help me with a formula for cell B1 on Sheet 1? I want to go to sheet 2 for a given property and sum the values on begining five rows down and two columns to the right of the reference for 3 consecutive columns. My attempt
=Sum(OFFSET(VLOOKUP(A1,'Sheet2'!A1:K350,1,FALSE),5,2,1,3)) fails.

In certain situations, I do not want to sum - just select up a number related to that property. Again, assuming that number I want is 6 rows down and 12 columns to the right, the formula
=OFFSET(VLOOKUP(A1,'Sheet2'!A1:K350,1,FALSE),6,12) fails

I believe tha issue is that the vlookup reference is on one sheet and the offset properties belong to another sheet. I would appreciate any help.

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
0
Sorry - not smart enough to take an extract to show you a readable example.
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.
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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

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