Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use vlookup within offset?
Can I use a vlookup within an offset formula to define the reference? I want
to find a value in a column on a tab, go down one, and record the anwser. On sheet2 in cell c7 I type the formula: =offset(vlookup(b7,'sheet1!$f$1:$f$182),1,0),1,0) b7 is defined by another lookup formula so it's variable depending on an input cell. I've used index/match successfully for this purpose, but vlookup gives me "the formula you typed contains an error." with no explanation of the error. What I'm doing is converting a column of entries on sheet1 to a horizontal sequence on a form that is sheet2. I want the user to input a code into cell A1 on sheet2. This generates the value in b7. I want to find the cell below the value in b7 on a list in sheet1 and put it in c7. Then I want d7 to look for c7 in the list and record the value below it. The list has to stop when certain things change, but that's another issue. I have to solve this one first. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use vlookup within offset?
I've used index/match successfully for this purpose,
That's what you want to use. but vlookup gives me "the formula you typed contains an error." with no explanation of the error. =offset(vlookup(b7,'sheet1!$f$1:$f$182),1,0),1,0) The error is being caused by OFFSET. The first argument must be a reference. VLOOKUP returns a value not a reference. -- Biff Microsoft Excel MVP "berniean" wrote in message ... Can I use a vlookup within an offset formula to define the reference? I want to find a value in a column on a tab, go down one, and record the anwser. On sheet2 in cell c7 I type the formula: =offset(vlookup(b7,'sheet1!$f$1:$f$182),1,0),1,0) b7 is defined by another lookup formula so it's variable depending on an input cell. I've used index/match successfully for this purpose, but vlookup gives me "the formula you typed contains an error." with no explanation of the error. What I'm doing is converting a column of entries on sheet1 to a horizontal sequence on a form that is sheet2. I want the user to input a code into cell A1 on sheet2. This generates the value in b7. I want to find the cell below the value in b7 on a list in sheet1 and put it in c7. Then I want d7 to look for c7 in the list and record the value below it. The list has to stop when certain things change, but that's another issue. I have to solve this one first. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use vlookup within offset?
Hi bernian,
Since index/match worked previously, and your stated needs are easily met with index/match, is there something else which has triggered your attempt at using vlookup? Steve. "berniean" wrote in message ... Can I use a vlookup within an offset formula to define the reference? I want to find a value in a column on a tab, go down one, and record the anwser. On sheet2 in cell c7 I type the formula: =offset(vlookup(b7,'sheet1!$f$1:$f$182),1,0),1,0) b7 is defined by another lookup formula so it's variable depending on an input cell. I've used index/match successfully for this purpose, but vlookup gives me "the formula you typed contains an error." with no explanation of the error. What I'm doing is converting a column of entries on sheet1 to a horizontal sequence on a form that is sheet2. I want the user to input a code into cell A1 on sheet2. This generates the value in b7. I want to find the cell below the value in b7 on a list in sheet1 and put it in c7. Then I want d7 to look for c7 in the list and record the value below it. The list has to stop when certain things change, but that's another issue. I have to solve this one first. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use vlookup within offset?
Biff: I was afraid that was the reason.
Steve: Yes, and it has to do with the second part of the problem. Sheet1 is a vertical list of product SKU's by style. Sheet2 is a form that will be exported as a product specification sheet. It lists the SKU's horizontally. Cell A1 is where the user inputs a product code which should retreive all the SKU's for that product in cells B7-F7. Sometimes there will be 1 SKU, sometimes 5. For some reason, I don't always get all of the related SKU's. B7 is a straight vlookup for the product code. C7-F7 compare the style name in B8-E8 with the style name for the SKU in Sheet1. If it is the same, I get the SKU. If it is different, I get "". This is the formula: =IF(VLOOKUP(OFFSET(INDEX('Sheet1'!$F$1:$M$182,MATC H(B8,'Sheet1'!$M$1:$M$182,0),1),1,0),'Sheet1'!$F$1 :$M$182,8,0)=LookupV,OFFSET(INDEX('Sheet1'!$F$1:$M $182,MATCH(B8,'Sheet1'!$M$1:$M$182,0),1),1,0),"") B8 on Sheet2 has the style name. It is also a vlookup on the product code. The offset row increases by one for each column on Sheet2. Column F in Sheet1 is the SKU list, and column M is the style name list. Again, it works for some, but not for all and I can't see a reason for that. Consequently, I've been trying to find a different, hopefully simpler way to do this. I realize how difficult it is to explain in this forum which is why I asked the simple question first! Thanks! "berniean" wrote: Can I use a vlookup within an offset formula to define the reference? I want to find a value in a column on a tab, go down one, and record the anwser. On sheet2 in cell c7 I type the formula: =offset(vlookup(b7,'sheet1!$f$1:$f$182),1,0),1,0) b7 is defined by another lookup formula so it's variable depending on an input cell. I've used index/match successfully for this purpose, but vlookup gives me "the formula you typed contains an error." with no explanation of the error. What I'm doing is converting a column of entries on sheet1 to a horizontal sequence on a form that is sheet2. I want the user to input a code into cell A1 on sheet2. This generates the value in b7. I want to find the cell below the value in b7 on a list in sheet1 and put it in c7. Then I want d7 to look for c7 in the list and record the value below it. The list has to stop when certain things change, but that's another issue. I have to solve this one first. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use vlookup within offset?
I would need to see the file (or a resonably accurate sample file) to try to
figure out what you're wanting to do. If you can post the file (or a sample file) to some link where I can download it I'll take a look at it. -- Biff Microsoft Excel MVP "berniean" wrote in message ... Biff: I was afraid that was the reason. Steve: Yes, and it has to do with the second part of the problem. Sheet1 is a vertical list of product SKU's by style. Sheet2 is a form that will be exported as a product specification sheet. It lists the SKU's horizontally. Cell A1 is where the user inputs a product code which should retreive all the SKU's for that product in cells B7-F7. Sometimes there will be 1 SKU, sometimes 5. For some reason, I don't always get all of the related SKU's. B7 is a straight vlookup for the product code. C7-F7 compare the style name in B8-E8 with the style name for the SKU in Sheet1. If it is the same, I get the SKU. If it is different, I get "". This is the formula: =IF(VLOOKUP(OFFSET(INDEX('Sheet1'!$F$1:$M$182,MATC H(B8,'Sheet1'!$M$1:$M$182,0),1),1,0),'Sheet1'!$F$1 :$M$182,8,0)=LookupV,OFFSET(INDEX('Sheet1'!$F$1:$M $182,MATCH(B8,'Sheet1'!$M$1:$M$182,0),1),1,0),"") B8 on Sheet2 has the style name. It is also a vlookup on the product code. The offset row increases by one for each column on Sheet2. Column F in Sheet1 is the SKU list, and column M is the style name list. Again, it works for some, but not for all and I can't see a reason for that. Consequently, I've been trying to find a different, hopefully simpler way to do this. I realize how difficult it is to explain in this forum which is why I asked the simple question first! Thanks! "berniean" wrote: Can I use a vlookup within an offset formula to define the reference? I want to find a value in a column on a tab, go down one, and record the anwser. On sheet2 in cell c7 I type the formula: =offset(vlookup(b7,'sheet1!$f$1:$f$182),1,0),1,0) b7 is defined by another lookup formula so it's variable depending on an input cell. I've used index/match successfully for this purpose, but vlookup gives me "the formula you typed contains an error." with no explanation of the error. What I'm doing is converting a column of entries on sheet1 to a horizontal sequence on a form that is sheet2. I want the user to input a code into cell A1 on sheet2. This generates the value in b7. I want to find the cell below the value in b7 on a list in sheet1 and put it in c7. Then I want d7 to look for c7 in the list and record the value below it. The list has to stop when certain things change, but that's another issue. I have to solve this one first. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Offset help | Excel Worksheet Functions | |||
VLOOKUP and OFFSET | Excel Worksheet Functions | |||
offset within vlookup | Excel Discussion (Misc queries) | |||
Using Offset with Vlookup | Excel Discussion (Misc queries) | |||
VLOOKUP with OFFSET | Excel Worksheet Functions |