From: Cecilia on 13 Apr 2010 21:17 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!!
From: T. Valko on 13 Apr 2010 21:49 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)  Biff Microsoft Excel MVP "Cecilia" <Cecilia(a)discussions.microsoft.com> wrote in message news:6AD6F517ABD140F09AF8713C2B776197(a)microsoft.com... > 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!!
From: Jacob Skaria on 14 Apr 2010 00:47 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.  Jacob (MVP  Excel) "Cecilia" wrote: > 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!!

Pages: 1 Prev: Countif for finding a Time input Next: Use Vlookup to store formulas? 