Excel-how to lock down "table array" values in vlookup, columns numbered

3
votes

I am using Excel 2013 at my workplace and the cell columns are numbered instead of alphabetized.

enter image description here

With that in mind, these are my table array values for my vlookup formula that excel automatically populated for me as I used my mouse to highlight the table area.

Sheet1!R[2]C[-3]:R[6]C[-2]

How do I lock down these values? I tried things like

Sheet1!$R$[2]$C$[-3]:$R$[6]$C$[-2] but it doesn't work.

This is my current vlookup formula.

=VLOOKUP(RC[-1],Sheet1!R[2]C[-3]:R[6]C[-2],2,FALSE)

Below is the table array source that I am trying to reference.

enter image description here

Please advise! Thank you!

ANSWER: Below is the formula I am using now:

=VLOOKUP(RC[-1],Sheet1!R4C1:R8C2,2,FALSE)

it is locked in :-)

2
excelvlookup
R1C1 without brackets is absolute – QHarr
Thank you @QHarr ! – user4851649

2 Answers

1
votes

In R1C1 notation you miss out the square brackets for absolute or include the brackets (around the number) for relative.

References:

R1C1, the unused Excel cell reference system

See discussion also here.

MSDN Address Property:

Example The following example displays four different representations of the same cell address on Sheet1. The comments in the example are the addresses that will be displayed in the message boxes.

Set mc = Worksheets("Sheet1").Cells(1, 1)
MsgBox mc.Address()                              ' $A$1
MsgBox mc.Address(RowAbsolute:=False)            ' $A1
MsgBox mc.Address(ReferenceStyle:=xlR1C1)        ' R1C1
MsgBox mc.Address(ReferenceStyle:=xlR1C1, _
    RowAbsolute:=False,     _
    ColumnAbsolute:=False,  _
    RelativeTo:=Worksheets(1).Cells(3, 3))        ' R[-2]C[-2]
0
votes

You can change back to alphabetized columns by changing this setting in Excel options:

enter image description here