+ Reply to Thread
Results 1 to 7 of 7

Return value 2 columns to left of cell in single row

  1. 06-05-2016, 06:23 PM #1
    AndyGW
    AndyGW is offline
    Registered User
    Join Date
    05-03-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    95

    Return value 2 columns to left of cell in single row

    I must be having a brain freeze here because I just cannot do this. I want to find a value in a single row and return the value 3 columns to the left of it. I've tried vlookup, match and offset but to no avail. Please help.
    Last edited by AndyGW; 06-05-2016 at 06:32 PM.
    Reply With QuoteRegister To Reply
  2. 06-05-2016, 06:28 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Return value 2 columns to left of cell in single row

    Confusing: 2 or 3?

    One way:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    or maybe
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Whatever ...
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 06-05-2016, 06:43 PM #3
    AndyGW
    AndyGW is offline
    Registered User
    Join Date
    05-03-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: Return value 2 columns to left of cell in single row

    Sorry double type I meant 3 and to the RIGHT not LEFT

    Here's more detail

    I have a row 20 or so columns wide with several names followed by a value 3 columns to the right of each, so eg Andy | null | null | 3 | Adrian | null | null | 2
    When I put in =INDEX(F13:AK13,COLUMN(BQ12)+3), with BK12 being the name $ i get REF
    Reply With QuoteRegister To Reply
  4. 06-05-2016, 06:46 PM #4
    jason.b75
    jason.b75 is offline
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Return value 2 columns to left of cell in single row

    Try

    =INDEX(I13:AK13,MATCH(BK12,F13:AH13,0))

    Note that the range in INDEX is 3 columns right of the range in MATCH
    Reply With QuoteRegister To Reply
  5. 06-05-2016, 06:59 PM #5
    AndyGW
    AndyGW is offline
    Registered User
    Join Date
    05-03-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: Return value 2 columns to left of cell in single row

    Perfect, thanks
    Reply With QuoteRegister To Reply
  6. 06-05-2016, 07:02 PM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Return value 2 columns to left of cell in single row

    Why not just ...
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 06-05-2016, 07:03 PM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Return value 2 columns to left of cell in single row

    Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] vLookup multiple values separated by ";" in single cell / return result in single cell
    By Tunesmith in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2015, 12:02 PM
  2. [SOLVED] Basic function help, Subtract LEFT from RIGHT String in single cell :(
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2014, 11:51 AM
  3. formula - value lookup, but return value from x columns left and y rows down
    By Armitage2k in forum Excel General
    Replies: 4
    Last Post: 12-03-2012, 07:30 AM
  4. Keyboard shortcut for single left click in Excel cell
    By johnnylzep in forum Excel General
    Replies: 1
    Last Post: 09-04-2012, 04:07 AM
  5. left() for single cell?
    By mpkavanagh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-12-2011, 06:30 PM
  6. Filtering Across Columns and return in a single column
    By keith_chia in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2008, 07:13 AM
  7. Using Multiple Columns in a Lookup to return a single value
    By excelCPA in forum Excel General
    Replies: 2
    Last Post: 08-31-2006, 03:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1