Unclear Vlookup parameter

R1C1 notation

Anything appearing within [ ] post R/C is a relative adjustment in relation to the cell in which the formula is being posted.

Assume ActiveCell is J10 then

R[2]C

refers to J12 ... 2 rows down, no adjustment on Column

RC[2]

refers to L10 ... no adjustment on Row, 2 columns to Right

R[-3]C[-1]

refers to I7 ... 3 rows up, 1 column left

RC

refers to J10 ... no adjustment

Any numbers that appear post R/C not encased within [ ] are absolute references, again, with J10 as Active Cell

R1C

refers to J$1 ... the Row is absolute, no adjustment to Column which remains "relative"

R1C3

refers to $C$1 ... both Row & Column are now Absolute

R1C[1]

refers to K$1 ... Row is absolute but Column is adjusted one to the right.

The reference need not contain both row & column ... ie to refer to an entire row

R[-3]

refers to 7:7

and to refer to an entire column

C[1]

which would refer to K:K


So taking all of that and for sake of demo (and continuity) we assume Active cell to be J10 then your formula:

=VLOOKUP(RC[-1],LOOKUP!C[-7]:C[-6],2,FALSE)

is basically translated as:

=VLOOKUP(I10,LOOKUP!C:D,2,FALSE)

LOOKUP as used here is the name of a worksheet, rather than a function, which is why it is followed by an exclamation mark.