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,

	A	B	C	D	E	F	G	H
1			Jon				4
2			Abe				12.5

<tbody>
</tbody>

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