How to change index value inside VLOOKUP

dmadhup · May 30, 2018

I have following code:

For i = 8 To EndRow

Range("H" & i) = "=VLOOKUP(A & i,$P:$P, 1,FALSE)"
Next i

I want to change the lookup value as the value of I changes. Like A8, A9, A10 ................

Any help is appreciated. Thank you

StephenCrump · May 30, 2018

Code:

For i = 8 To EndRow
    Range("H" & i).Formula = "=VLOOKUP(A" & i & ",$P:$P,1,FALSE)"
Next i

But you could do it without looping:

Code:

Range("H8:H" & EndRow).Formula = "=VLOOKUP(A8,$P:$P,1,FALSE)"

Given you're looking in the first column, you could also replace the VLOOKUP with a simpler MATCH.