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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com