= VLOOKUP(RC[-1],Sheet1!C[-2]:C[-1],1,0)

First, the table range Sheet1!C[-2]:C[-1] is not valid in this context. Relative C[] references only work properly inside R1C1-style formulas when attached to a specific cell reference. For a lookup table, you normally need explicit column references such as Sheet1!C1:C2 or a defined range.

Second, the column index argument is set to 1. In VLOOKUP, the lookup value must be found in the first column of the table array, and the formula returns a value from the column number you specify. Using 1 simply returns the matched value from the first column, not the adjacent column.

If you want to look up the value from the previous column (RC[-1]) in Sheet1 column A and return the corresponding value from column B, the corrected R1C1-style formula would be

=VLOOKUP(RC[-1],Sheet1!C1:C2,2,FALSE)

or

=VLOOKUP(RC[-1],Sheet1!C1:C2,2,0)

This tells Excel to:

• look up the value in the previous column,
• search the first column of the range on Sheet1,
• return the value from the second column,
• require an exact match.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin