Can I use LOOKUP as the reference in OFFSET function?

[Guest]

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

 

[Peo Sjoblom]

Use INDEX and MATCH instead

=INDEX(Y3:Y4000,MATCH(B33,O3:O40000,0)-1)

might work

 

[Guest]

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?

 

[Earl Kiosterud]

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

 

[Guest]

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?

 

[David Biddulph]

The functions (with the singular exception of DATEDIF) are listed at
http://office.microsoft.com/en-us/excel/HP052042111033.aspx
(or Excel help)
--
David Biddulph

....

By the way, can you suggest where I can go to learn more about advanced
Excel functions?

....