vlookup and offset

[Rob M.]

I am trying to use and offset that starts the reference by utilizing a
vlookup .

OFFSET(VLOOKUP(D3,'02-00W-In$ight'!O7:X19,1,0),0,-9)
This is what I have - it returns an error saying the formula contains an error

help is appreciated.. thanks

[Rowan Drummond]

Try a combination of Index and Match something like:

=INDEX('02-00W-In$ight'!F7:F19,MATCH(D3,'02-00W-In$ight'!O7:O19,0),1)

Hope this helps
Rowan

Rob M. wrote:
> I am trying to use and offset that starts the reference by utilizing a
> vlookup .
>
> OFFSET(VLOOKUP(D3,'02-00W-In$ight'!O7:X19,1,0),0,-9)
> This is what I have - it returns an error saying the formula contains an error
>
> help is appreciated.. thanks

[Dave Breitenbach]

Rowan is correct. To elaborate...Offset needs a reference - not a value.
Your vlookup is returning a value of the contents d3 - not "d3", since your
asking it to return what matches the contents of d3 in the first column of
your lookup range.
Index/Match can return a reference to a cell which offset can understand.


"Rob M." wrote:

> I am trying to use and offset that starts the reference by utilizing a
> vlookup .
>
> OFFSET(VLOOKUP(D3,'02-00W-In$ight'!O7:X19,1,0),0,-9)
> This is what I have - it returns an error saying the formula contains an error
>
> help is appreciated.. thanks

[Max]

Some thoughts.. On the face of it, if the VLOOKUP evaluates to a defined
range, think we could try wrapping INDIRECT around the VLOOKUP, so something
like this should work:

= OFFSET(INDIRECT(VLOOKUP(D3,'02-00W-In$ight'!O7:X19,1,0)),0,-9)

The above OFFSET expression would need to be array-entered* if the defined
range returned is a multi-cell range, should the expression be in a cell on
it's own, i.e. not nested within other functions.

*press CTRL+SHIFT+ENTER, instead of just ENTER

> ... VLOOKUP(D3,'02-00W-In$ight'!O7:X19,1,0)
But .. think the VLOOKUP above evaluates the same as just having: = D3 ? If
so, perhaps simply having:

= OFFSET(INDIRECT(D3),0,-9)

would do it
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Rob M." <Rob M.@discussions.microsoft.com> wrote in message
news:D0528BF7-15BB-4B79-BEF4-65D448D3876F@microsoft.com...
> I am trying to use and offset that starts the reference by utilizing a
> vlookup .
>
> OFFSET(VLOOKUP(D3,'02-00W-In$ight'!O7:X19,1,0),0,-9)
> This is what I have - it returns an error saying the formula contains an
error
>
> help is appreciated.. thanks