Find cell relative to starting point based on matching reference from another sheet

[kidneythief]

Hi! First off, the sheet in Sample.jpg comes in that layout and I am unable to change it.
In a separate sheet, I will be inputting any of the green shaded values in column A of Sample. With just that data, I am
hoping to pull the corresponding yellow shaded value from column E of Sample.

So for example, if in the separate sheet I input 10001 in A1, I would like B1 to pull the matching yellow shaded value, or 40.
If the input is 10002 , B2 should return 70; if it's 10003, B2 should return 30.

Is this possible? I've been trying different permutations of Index, Match, Offset, etc. to no avail. Thank you!

 

[James006]

Hi,
You can test following

=OFFSET(INDIRECT("Sheet1!A"&MATCH(A1,Sheet1!A:A,0)),4,4)

 

[kidneythief]

Hi,
You can test following

=OFFSET(INDIRECT("Sheet1!A"&MATCH(A1,Sheet1!A:A,0)),4,4)

Hi James006! I seem to be getting a #REF error, but also, would the offset still work since each range in Sample.jpg are
different sizes? The one with 10001 for instance has 6 total rows while the range beginning with 10002 has 7 total rows.
The row numbers vary greatly in the actual data set used

 

[kidneythief]

I was thinking of somehow starting from the reference green cell, moving down to the first isnumber value, then offsetting 4 spaces to the right.
So if I'm starting at 10002, I was thinking of finding my way to 4 in A14, then moving to the right to 70 in E14. Not sure if that makes sense though

 

[kidneythief]

Find next cell within column that is a number, starting from reference cell in another sheet

I think this sounds simple but I'm having a bit of trouble figuring it out. I have 2 sheets, the Sample Sheet attached and an Input Sheet. In the Input Sheet, I input one of the green shaded values (

superuser.com

 

[James006]

Hi,
You could test the following

Sub myVal()
Dim rng As Range
Dim x As Long
Set rng = Sheet2.Range("A1")
x = Application.Match(rng, Sheet1.Range("A:A"), 0)
Sheet2.Range("B1") = Sheet1.Range("A" & x).Offset(Sheet1.Range("A" & x).CurrentRegion.Rows.Count - 2, 4).Value
End Sub

Should you find this solution acceptable, it can always be improved with a Double-Click Event ...
Edit:
As a matter of fact, in the Sheet2 Module, you can test this modification:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim x As Long
x = Application.Match(Target, Sheet1.Range("A:A"), 0)
Target.Offset(0, 1) = Sheet1.Range("A" & x).Offset(Sheet1.Range("A" & x).CurrentRegion.Rows.Count - 2, 4).Value
Cancel = True
End Sub