vlookup and offset

R

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
 
R

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
 
D

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.
 
M

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)
Click to expand...
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
 
Top