# Thread: VLOOKUP result with VERSCHUIVING (OFFSET???) (2002 SP3)

1. ## VLOOKUP result with VERSCHUIVING (OFFSET???) (2002 SP3)

Hi,

See attachment for an example
I have a table of data which I use for a VLOOKUP. The problem is that one result should be the row above the result for another VLOOKUP. Since I already can find the result one row below my objective I think nudging the result one row should not be that big a deal. But for me it seems to be.

In the example is only 1 block of data but in the real table the block will be repeated many times with only one time a value in column B per block.

Does anyone know how to do this?

Regards Marcel de Vlieger

2. ## Re: VLOOKUP result with VERSCHUIVING (OFFSET???) (2002 SP3)

You could use the following formula in F25:
<code>
=INDEX(\$F\$2:\$H\$19,MATCH(B25,\$B\$2:\$B\$19,0),D21)
</code>
In Dutch:
<code>
=INDEX(\$F\$2:\$H\$19;VERGELIJKEN(B25;\$B\$2:\$B\$19;0);D2 1)
</code>
And in G25:
<code>
=INDEX(\$F\$2:\$H\$19,MATCH(B25,\$B\$2:\$B\$19,0)-1,D21)
</code>
In Dutch:
<code>
=INDEX(\$F\$2:\$H\$19;VERGELIJKEN(B25;\$B\$2:\$B\$19;0)-1;D21)
</code>
The only difference between the two formulas is the -1 after the MATCH function; this is used to retrieve the value from the row above the other one.

Adjust the ranges B2:B19 and F2:H19 as needed.

3. ## Re: VLOOKUP result with VERSCHUIVING (OFFSET???) (2002 SP3)

Hi Hans,

Regards Marcel

4. ## Re: VLOOKUP result with VERSCHUIVING (OFFSET???) (2002 SP3)

Hi Hans,

I tested it and it worked like a charm on the example sheet I send. But in the real situation it does not work.
In the real situation the output is on a different sheet as the source data. I updated all the references to the real workbook but it does not work.

Does any of the formulas have a limit concerning refering to different sheets?

Regards
Marcel

5. ## Re: VLOOKUP result with VERSCHUIVING (OFFSET???) (2002 SP3)

No, you should be able to change any of the ranges to refer to another sheet (or even another workbook).

6. ## Re: VLOOKUP result with VERSCHUIVING (OFFSET???) (2002 SP3)

Oops my error. I had 2 columns in the MATCH part of the formula.

Regards Marcel

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•