Just curious, is it possible to combine Vlookup() and Offset() together in one formula? I would like to lookup a certain value in a range, and based on the location of the value, go down two rows and one column to the right and then return this value.
Example:
Please Login or Register to view this content.
Possible? Or do I need to have a go at Index()? Not really familiar with Index() so any help would be great.
Thanks
The VLOOKUP doesn't sem all that relevant. All you want is to find the position of apple in your array and then offset from there. I got a bit lost: so this is almost certainly unnecessarily complicated, but it seems to work...
=OFFSET(INDIRECT(CELL("address",INDIRECT("A"&MATCH(A4,$A$1:$A$10,0)))),2,2)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
if =vlookup("apple","A1:B10",2,FALSE) gives value in b4
match("apple",a1:a10,0) would give row 4
match("apple",a1:a10,0)+2 would give row 6
now given that index(c1:c10,6) is c6
so replace the 6 with the match ie match("apple",a1:a10,0)+2
so simply
=INDEX($C$1:$C$10,MATCH("apple",$A$1:$A$10,0)+2)
returns the value in c6
Last edited by martindwilson; 11-16-2014 at 06:23 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Yea... I just knew I was overcomplicating it!!!
this is a surprisingly boring solution to my problem, thanks guys
this is a surprisingly boring solution to my problem, thanks guys
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks