Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Backwards lookup (Excel professional)

    How can I create something that will essentially be a vlookup function that reads from right to left?

  2. #2
    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: Backwards lookup (Excel professional)

    Does this <post#=244408>post 244408</post#> answer your question?

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backwards lookup (Excel professional)

    I've used Steve's technique for years, even in cases where the normal VLOOKUP function would work fine. I don't like VLOOKUP because it depends on moving over a fixed number of columns to find the value you want. If you insert a column into your table, you can screw up your VLOOKUP functions. The INDEX/MATCH technique will automatically adjust for inserted columns.

    The same holds true for HLOOKUP.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backwards lookup (Excel professional)

    This method is so useful it would be much appreciated if one of our VBA genii could write it as as Wizard.

  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: Backwards lookup (Excel professional)

    Do you mean a custom function?
    To use the attached, enter:
    = NewLookup(LookupValue, LookupArray, OutputArray, MatchType)
    Where:
    LookupValue is the value to "lookup"
    LookupArray is the range
    OutputArray is the range for where the value will be obtained from
    MatchType is optional (like from MATCH)
    1 for ascending
    0 for exact
    -1 for descending

    Like match, if omitted it is assumed = 1

    LookupValue can be cell reference
    Arrays may either be in a column or in a row. They actually could be transposed from one another and DO NOT have to be the same size, though if the lookupvalue is outside the size of the output, you will get an error.

    Steve

    <pre>Option Explicit
    Function NewLookup(LookupValue, LookupArray As Range, _
    OutputArray As Range, Optional MatchType As Integer = 1)

    Dim af As WorksheetFunction
    Set af = Application.WorksheetFunction
    NewLookup = af.Index(OutputArray, af.Match(LookupValue, LookupArray, MatchType))
    End Function</pre>


  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backwards lookup (Excel professional)

    Yes Steve. Thank you.

Posting Permissions

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