Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Vlookup to the left? (Excel 2000)

    Is there a function or add-on that is available that can do a lookup to the left of the main reference column, instead of exclusively to the right? I have data tables where the normal layout is best for most purposes, but sometimes I have to lookup a value to the left. To do this I would have duplicate the data into additional columns to perform a standard right-hand lookup, or else write some insanely long hybrid of lookup, match, and/or index formulas.
    <hr>=VLOOKUP'(I2,Sheet1!A:H,4,2,FALSE) => Lookup value in Range, locate value in Column 4, return value in Column 2, Unsorted<hr>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup to the left? (Excel 2000)

    Is LOOKUP so bad? Say your lookup value is in cell D1, the reference cells in column B, and the values to be returned in column A. Then this formula will perform a "lookup to the left":

    =LOOKUP(D1,B1:B37,A1:A37)

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Vlookup to the left? (Excel 2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I've actually used vlookups for so long that I never even considered a plain old 'lookup'! I always thought of it as an under-powered vlookup, but combined with column-header range names (such as are in nearly all of my data tables <img src=/S/doh.gif border=0 alt=doh width=15 height=15>) it may actually turn out to be a better option. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    =lookup(Data,SourceColumnName,ResultColumnName) - almost like programming in plain English!

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup to the left? (Excel 2000)

    Some things to keep in mind when using LOOKUP.

    1. if duplicates exist, LOOKUP always selects the *last* value in the row or column. VLOOKUP selects the *first*.

    2. With LOOKUP, the values must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. With VLOOKUP, If range_lookup is FALSE, table_array does not need to be sorted.

    bf

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup to the left? (Excel 2000)

    The method I proposed, (index and match) works just like VLOOKUP and does NOT have these limitations

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vlookup to the left? (Excel: 97-2002)

    The VLOOKUP and HLOOKUP functions can be extremely useful in excel. One of the "problems" with them is that the items to be "Looked-up" must be the the first COLUMN of the data set (for VLOOKUP) or the first ROW for HLOOKUP. If you want to do a VLOOKUP to the left or an HLOOKUP upward you have to either modify your table, or add a hidden column of duplicate data to accomplish this.

    One relatively straightforward way around this is to combine the INDEX feature with a MATCH.

    For example the formula:
    <pre>=INDEX(B:B,MATCH(I2,Sheet1!D,FALSE))</pre>

    will "lookup" the value in Cell I2 and find the ROW of the first exact match in Column D. It will then "pull out" (Using the INDEX) the value in Column B from that same row.


    Similarly the formula:
    <pre>=INDEX(1:1,MATCH(I2,Sheet1!5:5,FALSE))</pre>

    will "lookup" the value in Cell I2 and find the COLUMN of the first exact match in Row 5. It will then "pull out" (Using the INDEX) the value in Row 1 from that same column.

    Steve

Posting Permissions

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