Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: vlookup+offset+function

  1. #1
    Join Date
    11th February 2003
    Location
    Melbourne
    Posts
    27

    vlookup+offset+function

    Hi Im trying to lookup a series of values using the offset function, by reference to one cell only.
    Using a vlookup to get to c4, I then need to return 5 seperate values from various cells awayfrom c4. For eg. The 1st value is 1 row down and 5 across, another is 3 down and 1 across. These are seperate lookups, but Ive been using a nested lookup/offset fundtion which doesnt seem to work. here is it:
    =offset(VLOOKUP(B7,week,3,FALSE)),0,1,0). Any thoughts pls ??thx

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677

    Re: vlookup+offset+function

    OFFSET requires a Range (i.e. cell) reference to offset from.

    I don't see one in the above formula.

    Can you give an example of your sheet layout ?

    =OFFSET(reference,rows,cols,height,width)

    reference needs to be a cell address
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  3. #3
    Join Date
    11th February 2003
    Location
    Melbourne
    Posts
    27

    Re: vlookup+offset+function

    I have attached a copy of my sheet. Im trying to set up a worksheet function that will allow me to alter Timesheet!B7 and see the lookup results in the orange table. Its a timsheet with the source data on the Input tab. I thought offset and vlloup would be the best way.. thx
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677

    Re: vlookup+offset+function

    Quote Originally Posted by bradster
    I have attached a copy of my sheet. Im trying to set up a worksheet function that will allow me to alter Timesheet!B7 and see the lookup results in the orange table. Its a timsheet with the source data on the Input tab. I thought offset and vlloup would be the best way.. thx
    I used

    =OFFSET(INDIRECT("Input!A"&MATCH($B$7,Input!$A:$A,0)),$A11,C$14)

    for your cell C11...

    then I got carried away & completed the table

    See attached
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  5. #5
    Join Date
    11th February 2003
    Location
    Melbourne
    Posts
    27

    Re: vlookup+offset+function

    WIllR - You have outdone yourself. Thx very much. I look fwd to looking at the function in detail. Is this the easiest way to do a basic lookup?

    Thx again.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677

    Re: vlookup+offset+function

    Quote Originally Posted by bradster
    Is this the easiest way to do a basic lookup?

    Thx again.
    Not really, where you can, I would use VLOOKUP or INDEX/MATCH formulas.

    Your problem was that you didn't have a starting point to do either of the above in a straightforward way - hence I decided on INDIRECT - there are inevitably other ways to solve this - but this seems to work fine.
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  7. #7
    Join Date
    11th February 2003
    Location
    Melbourne
    Posts
    27

    Re: vlookup+offset+function

    Super stuff. Now I can get paid.... ;>

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Vlookup And Offset
    By Timbo in forum EXCEL HELP
    Replies: 4
    Last Post: June 22nd, 2007, 23:06
  2. Offset Vlookup
    By googlehoff in forum EXCEL HELP
    Replies: 15
    Last Post: January 7th, 2006, 03:38
  3. Offset or Vlookup
    By Timbo in forum EXCEL HELP
    Replies: 12
    Last Post: December 19th, 2005, 16:41
  4. VBA Vlookup using offset
    By excelnutter45 in forum EXCEL HELP
    Replies: 4
    Last Post: May 15th, 2005, 18:39
  5. Vlookup or Offset?
    By Timbo in forum EXCEL HELP
    Replies: 5
    Last Post: November 30th, 2004, 22:54

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno