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?

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?

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?

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?

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?