Formula for Range area within VLookup?

[BruceG]

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

 

[Bernard Liengme]

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

 

[BruceG]

That dud it.

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

Everyone, have a safe 4th.

Bruce

 

[Bernard Liengme]

Thanks for the feedback
I had a safe 1st as I'm a Canadian
best wishes