ho la chiave di ricerca (cioè il dato con cui comparare CODICE) in prima colonna e il dato che devo estrarre in colonna 2, con Lookup_Range = RANGE("A":"B") --> FUNZIONA.
PIPPO = Application.WorksheetFunction.VLookup(CODICE, Lookup_Range, 2, False)

ho la chiave di ricerca (cioè il dato con cui comparare CODICE) in terza colonna e il dato che devo estrarre in colonna 4, con Lookup_Range = RANGE("C":"D") --> NON FUNZIONA.
PIPPO = Application.WorksheetFunction.VLookup(CODICE, Lookup_Range, 4, False)

Selection.FormulaArray = "=VLOOKUP(RC[-1],CHOOSE({1,2},C[-3],C[-5]),2,0)"

Selection.FormulaArray = "=VLOOKUP(H9,CHOOSE({1,2},F:F,D:D),2,0)"

Sub EstraiDati()
Dim ur As Long
Dim lr As Long
Dim fr As Long
Dim rng As Range
Dim cel As Range
Dim tabDenSoc As Range
Dim tabDocum As Range
Application.ScreenUpdating = False
ur = Worksheets("INPUT").Cells(Rows.Count, 1).End(xlUp).Row
fr = Worksheets("ANAGRAFICA").Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Worksheets("INPUT").Range("B2:B" & ur)
Worksheets("RISULTATO ATTESO_BIS").Range("a2:C50000").ClearContents
Set tabDenSoc = Worksheets("ANAGRAFICA").Range("a2:b" & fr)
Set tabDocum = Worksheets("ANAGRAFICA").Range("c2:d" & fr)
For Each cel In rng
    lr = Worksheets("RISULTATO ATTESO_BIS").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 1).Value = Application.WorksheetFunction.VLookup(cel.Value, tabDenSoc, 2, False)
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 2).Value = cel.Value
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 3).Value = Application.WorksheetFunction.VLookup(Left(cel.Offset(0, -1).Value, 4), tabDocum, 2, False)
Next cel
Application.ScreenUpdating = True
End Sub

Sub EstraiDati()
Dim ur As Long
Dim lr As Long
Dim fr As Long
Dim rng As Range
Dim cel As Range
Dim tabDenSoc As Range
Dim tabDocum As Range
Dim tabGiorni As Range
Application.ScreenUpdating = False
Worksheets("INPUT").Select
Call TestoIndate
Worksheets("RISULTATO ATTESO_BIS").Activate
ur = Worksheets("INPUT").Cells(Rows.Count, 1).End(xlUp).Row
fr = Worksheets("ANAGRAFICA").Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Worksheets("INPUT").Range("d2:d" & ur)
Worksheets("RISULTATO ATTESO_BIS").Range("a2:C50000").ClearContents
Set tabDenSoc = Worksheets("ANAGRAFICA").Range("a2:b" & fr)
Set tabGiorni = Worksheets("Mesi").Range("a1:b7")
Set tabDocum = Worksheets("ANAGRAFICA").Range("c2:d" & fr)
For Each cel In rng
    lr = Worksheets("RISULTATO ATTESO_BIS").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 1).Value = Format(cel.Offset(0, 1).Value, "D mmmm")
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 2).Value = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.Text(Application.WorksheetFunction.Weekday(cel.Offset(0, 1).Value) + 1, "ddd"), tabGiorni, 2, False)
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 3).Value = Application.WorksheetFunction.VLookup(cel.Value, tabDenSoc, 2, False)
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 4).Value = cel.Value
    Worksheets("RISULTATO ATTESO_BIS").Cells(lr + 1, 5).Value = Application.WorksheetFunction.VLookup(Left(cel.Offset(0, -2).Value, 3), tabDocum, 2, False)
Next cel
Application.ScreenUpdating = True
End Sub


Sub TestoIndate()
Dim ur As Long
Dim cel As Range
ur = Worksheets("INPUT").Cells(Rows.Count, "E").End(xlUp).Row
For Each cel In Range("E2:e" & ur)
cel.Value = CDate(cel.Value)
Next cel
End Sub