------ Register to get rid of these "In Post" ads! ------


Is it possible to use a formula referenced by a cell as the range area within
Vlookup?

I am trying to do the following vlookup -

vlookup(j51,+p51,2)
j51 is a valid receipt number I am trying to look up for an invenotry item.

+p51 is a formula of other vlookup concatenated together to form a range
value -
it looks like AE$4:AH$30 done by the formula
=+"AE$"&+VLOOKUP(C51,B$8:G$22,5)&+":"&+("AH$"&+VLOOKUP(C51,B$8:G$22,6)) This
define the specific area of a particular inventory item and where all the
recepits. Each item has a different area and no set row amount.

For exxample Item ABC will be in rows 4-30 whereas CDE would be in rows
31-91. The receipts nuumber will alwats be in column AE of the receipt table
but the receipt # repeat for different items. Meaning Receipts #123 is in
both ABC & CDE.Which is why I was defining the item area first, then focusing
on receipt number.

2 is the column number in the rea of AE - AH.

The purpose of this lookup is to bring in a receipt # date so I can comppare
it against a sold date to find out how long an item stood arond in stock.

Is this possible or is there a better way to do this?

Thank you in advance for any help.

Bruce

------ Register to get rid of these "In Post" ads! ------


You can use the INDIRECT function

In A1:B 20 have
A 10
B 12
C 14
D 16
E 18
etc.....

In E1:G1 I have
A1:B10 C 14
The formula in G1 is =VLOOKUP(F1,INDIRECT(E1),2)
This is equivalent to =VLOOKUP(F1,A1:B10,2) and returns the value 14

So it looks like you need to use =vlookup(j51,INDIRECT(p51),2)

Please note that none of the plus signs (+) in any of your formulas are
needed.
Did you begin life a Lotus-123 users where + was needed?

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"BruceG" <BruceG@discussions.microsoft.com> wrote in message
news:E05BAA7C-ECFF-4BB3-BF7A-69EF8813E373@microsoft.com...
> Is it possible to use a formula referenced by a cell as the range area
> within
> Vlookup?
>
> I am trying to do the following vlookup -
>
> vlookup(j51,+p51,2)
> j51 is a valid receipt number I am trying to look up for an invenotry
> item.
>
> +p51 is a formula of other vlookup concatenated together to form a range
> value -
> it looks like AE$4:AH$30 done by the formula
> =+"AE$"&+VLOOKUP(C51,B$8:G$22,5)&+":"&+("AH$"&+VLOOKUP(C51,B$8:G$22,6))
> This
> define the specific area of a particular inventory item and where all the
> recepits. Each item has a different area and no set row amount.
>
> For exxample Item ABC will be in rows 4-30 whereas CDE would be in rows
> 31-91. The receipts nuumber will alwats be in column AE of the receipt
> table
> but the receipt # repeat for different items. Meaning Receipts #123 is in
> both ABC & CDE.Which is why I was defining the item area first, then
> focusing
> on receipt number.
>
> 2 is the column number in the rea of AE - AH.
>
> The purpose of this lookup is to bring in a receipt # date so I can
> comppare
> it against a sold date to find out how long an item stood arond in stock.
>
> Is this possible or is there a better way to do this?
>
> Thank you in advance for any help.
>
> Bruce
>
>
>

------ Register to get rid of these "In Post" ads! ------


That dud it.

Thank you, Bernard. And yes, I used to use 123.

Everyone, have a safe 4th.

Bruce

"Bernard Liengme" wrote:

> You can use the INDIRECT function
>
> In A1:B 20 have
> A 10
> B 12
> C 14
> D 16
> E 18
> etc.....
>
> In E1:G1 I have
> A1:B10 C 14
> The formula in G1 is =VLOOKUP(F1,INDIRECT(E1),2)
> This is equivalent to =VLOOKUP(F1,A1:B10,2) and returns the value 14
>
> So it looks like you need to use =vlookup(j51,INDIRECT(p51),2)
>
> Please note that none of the plus signs (+) in any of your formulas are
> needed.
> Did you begin life a Lotus-123 users where + was needed?
>
> best wishes
> --
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
> "BruceG" <BruceG@discussions.microsoft.com> wrote in message
> news:E05BAA7C-ECFF-4BB3-BF7A-69EF8813E373@microsoft.com...
> > Is it possible to use a formula referenced by a cell as the range area
> > within
> > Vlookup?
> >
> > I am trying to do the following vlookup -
> >
> > vlookup(j51,+p51,2)
> > j51 is a valid receipt number I am trying to look up for an invenotry
> > item.
> >
> > +p51 is a formula of other vlookup concatenated together to form a range
> > value -
> > it looks like AE$4:AH$30 done by the formula
> > =+"AE$"&+VLOOKUP(C51,B$8:G$22,5)&+":"&+("AH$"&+VLOOKUP(C51,B$8:G$22,6))
> > This
> > define the specific area of a particular inventory item and where all the
> > recepits. Each item has a different area and no set row amount.
> >
> > For exxample Item ABC will be in rows 4-30 whereas CDE would be in rows
> > 31-91. The receipts nuumber will alwats be in column AE of the receipt
> > table
> > but the receipt # repeat for different items. Meaning Receipts #123 is in
> > both ABC & CDE.Which is why I was defining the item area first, then
> > focusing
> > on receipt number.
> >
> > 2 is the column number in the rea of AE - AH.
> >
> > The purpose of this lookup is to bring in a receipt # date so I can
> > comppare
> > it against a sold date to find out how long an item stood arond in stock.
> >
> > Is this possible or is there a better way to do this?
> >
> > Thank you in advance for any help.
> >
> > Bruce
> >
> >
> >
>
>

------ Register to get rid of these "In Post" ads! ------


Thanks for the feedback
I had a safe 1st as I'm a Canadian
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"BruceG" <BruceG@discussions.microsoft.com> wrote in message
news:892E07FE-A911-4E8D-A9BD-536982C38D0B@microsoft.com...
> That dud it.
>
> Thank you, Bernard. And yes, I used to use 123.
>
> Everyone, have a safe 4th.
>
> Bruce
>
> "Bernard Liengme" wrote:
>
>> You can use the INDIRECT function
>>
>> In A1:B 20 have
>> A 10
>> B 12
>> C 14
>> D 16
>> E 18
>> etc.....
>>
>> In E1:G1 I have
>> A1:B10 C 14
>> The formula in G1 is =VLOOKUP(F1,INDIRECT(E1),2)
>> This is equivalent to =VLOOKUP(F1,A1:B10,2) and returns the value 14
>>
>> So it looks like you need to use =vlookup(j51,INDIRECT(p51),2)
>>
>> Please note that none of the plus signs (+) in any of your formulas are
>> needed.
>> Did you begin life a Lotus-123 users where + was needed?
>>
>> best wishes
>> --
>> Bernard Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>>
>> "BruceG" <BruceG@discussions.microsoft.com> wrote in message
>> news:E05BAA7C-ECFF-4BB3-BF7A-69EF8813E373@microsoft.com...
>> > Is it possible to use a formula referenced by a cell as the range area
>> > within
>> > Vlookup?
>> >
>> > I am trying to do the following vlookup -
>> >
>> > vlookup(j51,+p51,2)
>> > j51 is a valid receipt number I am trying to look up for an invenotry
>> > item.
>> >
>> > +p51 is a formula of other vlookup concatenated together to form a
>> > range
>> > value -
>> > it looks like AE$4:AH$30 done by the formula
>> > =+"AE$"&+VLOOKUP(C51,B$8:G$22,5)&+":"&+("AH$"&+VLOOKUP(C51,B$8:G$22,6))
>> > This
>> > define the specific area of a particular inventory item and where all
>> > the
>> > recepits. Each item has a different area and no set row amount.
>> >
>> > For exxample Item ABC will be in rows 4-30 whereas CDE would be in rows
>> > 31-91. The receipts nuumber will alwats be in column AE of the receipt
>> > table
>> > but the receipt # repeat for different items. Meaning Receipts #123 is
>> > in
>> > both ABC & CDE.Which is why I was defining the item area first, then
>> > focusing
>> > on receipt number.
>> >
>> > 2 is the column number in the rea of AE - AH.
>> >
>> > The purpose of this lookup is to bring in a receipt # date so I can
>> > comppare
>> > it against a sold date to find out how long an item stood arond in
>> > stock.
>> >
>> > Is this possible or is there a better way to do this?
>> >
>> > Thank you in advance for any help.
>> >
>> > Bruce
>> >
>> >
>> >
>>
>>