aryMonths = Array("","Jan","Feb","Mar","Apr","May","Jun", _
"Jul","Aug","Sep","Oct","Nov","Dec")
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10]," & _
aryMonths(ActiveCell.Offset(0, -1)) & ",2,FALSE)"

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"leitek.com" wrote in message
ups.com...
Hi there,

I am trying to create a nested if that look at column N and determine
the month number, then add the lookup function below based on month
number in column n. I am sure there is an easier way to do this. I
think I need to use a loop but I am not sure how it works.


Column N Column O
10 "=+VLOOKUP(RC[-10],Jan,2,FALSE)"
11
12
1
12

Sub test()

' lookup function based on left column value

'Do

If ActiveCell.Offset(0, -1) = 1 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Jan,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 2 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Feb,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 3 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Mar,2,FALSE)"
Else
If ActiveCell.Offset(0, -1) = 4 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Apr,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 5 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],May,2,FALSE)"
Else
If ActiveCell.Offset(0, -1) = 6 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Jun,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 7 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Jul,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 8 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Aug,2,FALSE)"
Else
If ActiveCell.Offset(0, -1) = 9 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Sep,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 10 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Oct,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 11 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Nov,2,FALSE)"

Else
If ActiveCell.Offset(0, -1) = 12 Then
ActiveCell.FormulaR1C1 = "=+VLOOKUP(RC[-10],Dec,2,FALSE)"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
'Loop Until Range("N:N") = True

End Sub

your help is greatly appreciated.

thanks