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:


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

Any suggestions?



T. Valko

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.

The error is being caused by OFFSET. The first argument must be a reference.
VLOOKUP returns a value not a reference.

Steve Dunn

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?



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:


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!




T. Valko

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question