Backwards lookup (Excel professional)

Topic

New Reply

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

Reply | Quote

Replies

Does this post 244408 answer your question?

Steve

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

Yes Steve. Thank you.

Reply | Quote

Yes Steve. Thank you.

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote