Should be simple, but missing something - Formula or VBA to return value 4 columns over from searched text

[pete3636]

I am working with a large spreadsheet that contains specific names and 4 columns over contains a numerical value. What I would like to be able to do is search for the specific text and then have the formula return the value to the right.

Example:

Pete is located in cell C4, C12, E1, G5.

In column G4 is a numeric value - this should be returned for the Pete found in C4

There are various text strings I would have to look up so I would just have to repeat the formula for each different text. I can already do the simple lookup to find pete, I just cannot figure out how to have it return the cell 4 columns over

Any help is greatly appreciated!

 

[Gingertrees]

Your answer can be found in the OFFSET function, which you could combine with a "find" function. For example,
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Abe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub findnames
Dim R as Range
Set R = Rows("1:100").Find(What:="Abe", LookIn:=xlFormulas, _ 
     LookAt:=xlPart, SearchOrder:=xlByRows)
If Not R Is Nothing Then
    Range("Z1") = R.value & " amount:"
    Range("Z1").Offset(0,1) = R.Offset(0,4).Value
End if
End sub

In the above example, Z1 = "Abe amount:", AA1 = 12.5