Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rob M.
 
Posts: n/a
Default vlookup and offset

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rowan Drummond
 
Posts: n/a
Default vlookup and offset

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Breitenbach
 
Posts: n/a
Default vlookup and offset

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default vlookup and offset

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 wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Offset And Vlookup Combo Help!! kollizion Excel Worksheet Functions 3 August 30th 05 05:07 AM
Vlookup then OFFSET over and down mendozalaura Excel Worksheet Functions 3 August 24th 05 12:32 AM
Which to use - if, vlookup, match, index, offset, vba? punsterr Excel Discussion (Misc queries) 3 June 7th 05 07:42 PM
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? Steve Excel Worksheet Functions 0 January 30th 05 10:11 PM
offset and vlookup cutsygurl Excel Worksheet Functions 1 November 5th 04 10:47 PM


All times are GMT +1. The time now is 09:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"