Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use LOOKUP as the reference in OFFSET function?
I would like to create a function that will lookup one particular cell in a
set range and instead of returning the value found in the range, make the output be the cell that is one row up and 10 colums over from the found cell in range. Here's what I've tried so far: =offset(VLOOKUP($B33,$O3:$Y40000,11,FALSE),-1,0,1,1) OR =offset(LOOKUP($B33,$O3:$O40000),-1,10,1,1) But Excel told me I had an error and it won't accept the equation. Can you use a LOOKUP function as the reference in the OFFSET function? If so, how should I write it? Thanks! Danni |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use LOOKUP as the reference in OFFSET function?
Use INDEX and MATCH instead
=INDEX(Y3:Y4000,MATCH(B33,O3:O40000,0)-1) might work -- Regards, Peo Sjoblom "Danni2004" wrote in message ... I would like to create a function that will lookup one particular cell in a set range and instead of returning the value found in the range, make the output be the cell that is one row up and 10 colums over from the found cell in range. Here's what I've tried so far: =offset(VLOOKUP($B33,$O3:$Y40000,11,FALSE),-1,0,1,1) OR =offset(LOOKUP($B33,$O3:$O40000),-1,10,1,1) But Excel told me I had an error and it won't accept the equation. Can you use a LOOKUP function as the reference in the OFFSET function? If so, how should I write it? Thanks! Danni |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use LOOKUP as the reference in OFFSET function?
Thanks for your quick response!
The equation almost works except I had to take the "-1" off of the end because it gave me "#VALUE!". When I took off the "-1", it returned the value of the cell just below the one I need. Suggestions? "Peo Sjoblom" wrote: Use INDEX and MATCH instead =INDEX(Y3:Y4000,MATCH(B33,O3:O40000,0)-1) might work -- Regards, Peo Sjoblom "Danni2004" wrote in message ... I would like to create a function that will lookup one particular cell in a set range and instead of returning the value found in the range, make the output be the cell that is one row up and 10 colums over from the found cell in range. Here's what I've tried so far: =offset(VLOOKUP($B33,$O3:$Y40000,11,FALSE),-1,0,1,1) OR =offset(LOOKUP($B33,$O3:$O40000),-1,10,1,1) But Excel told me I had an error and it won't accept the equation. Can you use a LOOKUP function as the reference in the OFFSET function? If so, how should I write it? Thanks! Danni |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use LOOKUP as the reference in OFFSET function?
Danni,
Your were close. Your formulas gave you trouble because OFFSET wants a cell reference (location), but the VLOOKUP is giving the value, not the reference to the cell. MATCH gives an index (a number, then number of rows into the column) to the cell where it found your value. Then you can use that in the OFFSET, to wit: =OFFSET(O2,MATCH($B33,O3:O40000,0)-1,11) -- Earl Kiosterud www.smokeylake.com Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Danni2004" wrote in message ... I would like to create a function that will lookup one particular cell in a set range and instead of returning the value found in the range, make the output be the cell that is one row up and 10 colums over from the found cell in range. Here's what I've tried so far: =offset(VLOOKUP($B33,$O3:$Y40000,11,FALSE),-1,0,1,1) OR =offset(LOOKUP($B33,$O3:$O40000),-1,10,1,1) But Excel told me I had an error and it won't accept the equation. Can you use a LOOKUP function as the reference in the OFFSET function? If so, how should I write it? Thanks! Danni |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use LOOKUP as the reference in OFFSET function?
Totally worked!
Thanks so much for your help! By the way, can you suggest where I can go to learn more about advanced Excel functions? "Earl Kiosterud" wrote: Danni, Your were close. Your formulas gave you trouble because OFFSET wants a cell reference (location), but the VLOOKUP is giving the value, not the reference to the cell. MATCH gives an index (a number, then number of rows into the column) to the cell where it found your value. Then you can use that in the OFFSET, to wit: =OFFSET(O2,MATCH($B33,O3:O40000,0)-1,11) -- Earl Kiosterud www.smokeylake.com Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Danni2004" wrote in message ... I would like to create a function that will lookup one particular cell in a set range and instead of returning the value found in the range, make the output be the cell that is one row up and 10 colums over from the found cell in range. Here's what I've tried so far: =offset(VLOOKUP($B33,$O3:$Y40000,11,FALSE),-1,0,1,1) OR =offset(LOOKUP($B33,$O3:$O40000),-1,10,1,1) But Excel told me I had an error and it won't accept the equation. Can you use a LOOKUP function as the reference in the OFFSET function? If so, how should I write it? Thanks! Danni |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I use LOOKUP as the reference in OFFSET function?
The functions (with the singular exception of DATEDIF) are listed at
http://office.microsoft.com/en-us/ex...042111033.aspx (or Excel help) -- David Biddulph "Danni2004" wrote in message ... .... By the way, can you suggest where I can go to learn more about advanced Excel functions? .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic reference in OFFSET function | Excel Worksheet Functions | |||
Lookup or reference function needed | Excel Worksheet Functions | |||
IF Function: Lookup value needs to reference other worksheets | Excel Worksheet Functions | |||
Lookup function returning reference, not value | Excel Worksheet Functions | |||
Can the offset worksheet function reference another worksheet | Excel Worksheet Functions |