Announcement

Collapse
No announcement yet.

vlookup+offset+function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    LinkedIn: Will Riley

    Comment


    • #3
      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

      Comment


      • #4
        Re: vlookup+offset+function

        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
        Kind Regards, Will Riley

        LinkedIn: Will Riley

        Comment


        • #5
          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.

          Comment


          • #6
            Re: vlookup+offset+function

            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

            LinkedIn: Will Riley

            Comment


            • #7


              Re: vlookup+offset+function

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

              Comment

              Working...
              X