Vlookup & relative cell reference?

C

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

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)
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top