Prev: Countif for finding a Time input
Next: Use Vlookup to store formulas?
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:6AD6F517-ABD1-40F0-9AF8-713C2B776197(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?