Question: I have defined a dynamic range using OFFSET function in Excel, and I use it in VLOOKUP function to lookup a value:

![enter image description here](https://i.stack.imgur.com/2Rc96.png)

```
=VLOOKUP($B6,OFFSET(C30,0,C4):OFFSET(C36,0,C4),2,0)

```

Problem is the range is not absolute, and it cannot lookup the value. If I hadn't used OFFSET function, I would define the range as follows, which would work:

```
=VLOOKUP($B6,$C$30:$C$36,2,0)

```

How can I make the following function's range absolute as above, so that the values can be looked up?

```
 =VLOOKUP($B6,OFFSET(C30,0,C4):OFFSET(C36,0,C4),2,0)

```

Answer: 
It isn't `OFFSET` that stops this working - your basic `VLOOKUP` won't work because the "table array" needs to be 2 columns at least if you have the "col\_index-num" as 2

What are you trying to do with this formula?

`=VLOOKUP($B6,$C$30:$C$36,2,0)`

If you want to look up B6 in C30:C36 and find the corresponding value in the next column you need to use this version

`=VLOOKUP($B6,$C$30:$D$36,2,0)`

Note "D" in place of second "C"

and for a corresponding fix to the `OFFSET` version you can use

`=VLOOKUP($B6,OFFSET(C30,0,C4):OFFSET(D36,0,C4),2,0)`
Use Address and Indirect function. For example:

from your example:

```
=VLOOKUP($B6,OFFSET(C30,0,C4):OFFSET(C36,0,C4),2,0)

```

should be:

```
=VLOOKUP($B6,INDIRECT(CONCATENATE(ADDRESS(ROW(C30),COLUMN(C30)+C4),":",ADDRESS(ROW(C30),COLUMN(C30)+C4))),2,0)

```