Quote:
I got it kinda working but still requires me to enter/change the 1st variable for each lookup,
Subandi you're kinda asking 2 questions.
First one requires the use of dollarsigns:
You use dollar-signs to tell Excel not to auto-change your range as you drag it down/right/left/up etc.
Put =A1 in B1 and drag it down gives =A2.
Put =A$1 in B1 and drag it down gives =A1
Put =$A1 in B1 and drag it right gives =A1
Depending where you put the dollarsign (in front of column or row) Excel won't change it. Obv you can use 2 dollarsigns to fix a position forever.
IF you ever drag a range to use in a formula, hit F4 to toggle the different options.
So you're solution would be:
=vlookup(R3,A$1:B$70,2,false)
and dragged down.
Quote:
So is there something like a universal placeholder that says
"take the # of this field and put an R before it"
so it gives out R3 when put in Q3 and R4 when in Q4 and so on..?
This is a different question!
=VLOOKUP(INDIRECT(ADDRESS(ROW(),18)),$A$1:$B$70,2, false)
ADDRESS gives a 'cell' according to row/column. So ROW() gives 4 when put in row 4 (say Q4!) and 18 gives 'r' as R is the 18th column.
INDIRECT tells Excel to use the result of ADDRESS(ROW();18), which is $R$4, as a range.
So actually, when put in row 4, your formula looks like this:
=vlookup(R4,$A$1:$B$70,2,false)
But when put in row 1598 your formula looks like this:
=vlookup(R1598,$A$1:$B$70,2,false)
.
Hope this helps!