• Vlookup to the left… (Two double zero three)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Vlookup to the left… (Two double zero three)

    Author
    Topic
    WSRudi
    AskWoody Lounger
    October 31, 2006 at 12:58 pm #436764

    Can vlookup be tweeked to collect a value to the left of the lookup value?
    EG: =VLOOKUP(E1,A1:E1,3,FALSE)

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSrory
      AskWoody Lounger
      October 31, 2006 at 1:28 pm #1036422

      Nope – you have to use INDEX/MATCH:
      =INDEX(return_column,MATCH(lookup_val,lookup_col,0))

      Reply | Quote
      • WSRudi
        AskWoody Lounger
        October 31, 2006 at 1:41 pm #1036425

        Thats perfect rory…
        Tx

        PS: Congrats on your Star Post!!

        Reply | Quote
        • WSrory
          AskWoody Lounger
          October 31, 2006 at 3:32 pm #1036441

          Thank you kindly! smile
          One other thing to note about INDEX/MATCH:
          If you’re like me, you probably quite often need to pull data from a few columns in a table – e.g. for a given value, you need the data in columns 1-3, 5 and 11-13. If you use INDEX/MATCH instead, you can add a separate column with the MATCH part of the formula, then refer to this in the INDEX formula, so you only actually do the lookup to get the row number once, whereas with VLOOKUP you are performing the same lookup each time.
          Also, with a VLOOKUP, if you reference a big table, the VLOOKUP is affected by any change within that table; the INDEX/MATCH version is only affected by changes within the 2 referenced columns.

          You can probably tell which one I prefer! grin

          Reply | Quote
          • WSRudi
            AskWoody Lounger
            November 1, 2006 at 4:30 am #1036518

            I am starting to see the benefits myself. Thanx for sharing this info…it certainly does allow for greater flexibility. My biggest exposure to INDEX/MATCH has been when they are combined to create a 2-way lookup which is convenient for large data tables.

            Cheers for the insight!

            Reply | Quote
          • zeddy
            AskWoody_MVP
            November 1, 2006 at 11:14 am #1036546

            Hi Rory

            You are so right with advantages of having a separate matchRow column when you need data from multiple columns
            I also prefer to use Index/Match

            Sometimes, I need to use a matchCol as well.
            Because of the variable nature of some data sheets, I end up using the results of my row and column ‘matches’ with the OFFSET function ‘anchored’ to $A$1 on a particular sheet.
            (Unfortunately, you have to remember to subtract 1 from each result to get the data item you need)

            I could probably use combinations of the INDIRECT and ADDRESS to get what I need.
            I guess what I really would like is worksheet function that works something like the VBA equivalent CELLS(row,col)

            zeddy

            Reply | Quote
            • WSshades
              AskWoody Lounger
              November 1, 2006 at 6:38 pm #1036666

              To avoid the “subtract 1” problem, I set up the data with an extra column between row headers and data, and columns headers and data. Then it is automatically adjusted. Then I also include one extra row at bottom and column to the right in the Data name definition. That way if I add data by inserting rows or columns inside the blank rows/columns, all formulas automatically adjust.

              I use this layout of data for just about every major project. It has saved considerable time, and formulas never have to be adjusted/changed.

              Reply | Quote
    • teunotn
      AskWoody Plus
      November 2, 2006 at 9:52 am #1036758

      Rudi,

      If you want to do interpolation (linear, spline, etc) at the same time in your lookup, try this http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm%5B/url%5D . It is very flexible because it uses separate X and Y lookup arrays that can be anywhere on the sheet,

      Teunis

      Reply | Quote
      • WSRudi
        AskWoody Lounger
        November 2, 2006 at 10:05 am #1036763

        Hey Teunis,

        Going above and beyond I see……thanx for the info. It will come in handy when I need it! Good to know I can tweek it further…
        Cheers

        Reply | Quote
    Viewing 1 reply thread
    Reply To: Vlookup to the left… (Two double zero three)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.