Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Vlookup to the left... (Two double zero three)

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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Vlookup to the left... (Two double zero three)

    Nope - you have to use INDEX/MATCH:
    <code>=INDEX(return_column,MATCH(lookup_val,lookup _col,0))</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup to the left... (Two double zero three)

    Thats perfect rory...
    Tx

    PS: Congrats on your Star Post!!
    Regards,
    Rudi

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Vlookup to the left... (Two double zero three)

    Thank you kindly! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup to the left... (Two double zero three)

    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!
    Regards,
    Rudi

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts

    Re: Vlookup to the left... (Two double zero three)

    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

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup to the left... (Two double zero three)

    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.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup to the left... (Two double zero three)

    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 . It is very flexible because it uses separate X and Y lookup arrays that can be anywhere on the sheet,

    Teunis

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup to the left... (Two double zero three)

    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
    Regards,
    Rudi

Posting Permissions

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