Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset And Vlookup Combo Help!! | Excel Worksheet Functions | |||
Vlookup then OFFSET over and down | Excel Worksheet Functions | |||
Which to use - if, vlookup, match, index, offset, vba? | Excel Discussion (Misc queries) | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions | |||
offset and vlookup | Excel Worksheet Functions |