Vlookup & relative cell reference?

[Cecilia]

Hello--

I have a formula entered as follows:

'=VLOOKUP(A8,Sheet1!$B$22:$O$22,2,FALSE)

My Sheet 1 - match column is this:

Row Column B
22 Store 1 - Primary
23 Store 1 - Secondary

I'd like to automatically have Vlookup return the values from Sheet 1, in
Row 23 without having to type that label in my original sheet, cell A9. Is
there any quick way to do this?

Thanks!!

 

[T. Valko]

If I understand what you're asking then you need to expand the range of your
lookup table.

'=VLOOKUP(A8,Sheet1!$B$22:$O$22,2,FALSE)

You're only using a single row as the lookup table in the formula above.

=VLOOKUP(A8,Sheet1!$B$22:$O$23,2,0)

 

[Jacob Skaria]

I understand you are looking to modify the formula such that when referencing
cell A9; there is no need to change the array part. Right now you have used
abosolute referencing (for a single row..)

'Could try the below which will change the row referencing when dragged down.
=VLOOKUP(A8,Sheet1!$B22:$O22,2,FALSE)

'OR could reference the entire column
=VLOOKUP(A8,Sheet1!B:O,2,0)

'OR the data range
=VLOOKUP(A8,Sheet1!$B$1:$O$1000,2,0)


Below are the different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.