Index Macro

[oleg_v]

Hi
I need some help:

I need a macro that will replace this function:=INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2)

I paste this function in cell B1 and pull down till B100

I addition i need in the same macro that if the value in cells a2,a3...contains the letter"T" replace the "+2" with "+3"

Thanks

[Bob Phillips]

Public Function LookupOffset(lookupValue As Variant, rng As Range)
Dim cell As Range

Set cell = rng.Columns(2).Find(lookupValue)
If Not cell Is Nothing Then

If InStr(cell.Offset(0, -1).Value, "T") > 0 Then

LookupOffset = cell.Offset(3, 1).Value
Else
LookupOffset = cell.Offset(2, 1).Value
End If
Else

LookupOffset = CVErr(xlErrNA)
End If

End Function


=LookupOffset(A2,Sheet1!A:C)

[oleg_v]

how
is it work?
is it do all the work or i need to paste some thing in the excel

i ment that the macro will do all

[Bob Phillips]

It can't do it all, something has to be doine in Excel, otherwise what is the point?

[oleg_v]

WHAT SHOULD I DO FOR IT TO WORK??

[Bob Phillips]

YOU SHOULD STOP SHOUTING TO START WITH, and explain exactly what it is that you want to do.

[oleg_v]

I am not shouting.
i have a function: =INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$B:$B,0)+2)
I need a macro that if in the A2 contains the letter"t" among the other letters in the cell to change "+2" to "+3"
and also if i put this function in the cell B2 it will automatical puls down till b1100:friends: