+ Reply to Thread
Results 1 to 4 of 4

How to offset a vlookup query by x columns to the left

  1. #1
    Registered User
    Join Date
    10-13-2011
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    3

    How to offset a vlookup query by x columns to the left

    Similar to the post at http://www.excelforum.com/excel-gene...x-columns.html, I want to pull data from a reference sheet. My problem is that the information is in a column to the left of the lookup data. I thought that the Offset function could pull data using a negative number from a row or column before the lookup data, but can only get a #N/A result. Attached is a sample file with my attempted formula: =VLOOKUP(B2,OFFSET(Reference!B:B,0,-1),1,FALSE)
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,640

    Re: How to offset a vlookup query by x columns to the left

    Try this instead in A2 of your lookup sheet:

    =INDEX(Reference!A:A,MATCH(B2,Reference!B:B,0))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-13-2011
    Location
    Pittsburgh, Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: How to offset a vlookup query by x columns to the left

    Thanks Pete!

    Worked perfectly.

    Dave

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,640

    Re: How to offset a vlookup query by x columns to the left

    Glad to hear it, Dave - thanks for feeding back.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1