Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Range area within VLookup?
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$"&+VLO OKUP(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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Range area within VLookup?
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" wrote in message ... 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$"&+VLO OKUP(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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Range area within VLookup?
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" wrote in message ... 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$"&+VLO OKUP(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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Range area within VLookup?
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" wrote in message ... 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" wrote in message ... 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$"&+VLO OKUP(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Area as dynamic range? | Excel Worksheet Functions | |||
How do I copy a vlookup formula without changing the data range? | Excel Worksheet Functions | |||
Scroll Area _Named Range in worksheet | Excel Discussion (Misc queries) | |||
Print area/range | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |