Backwards lookup (Excel professional)

[stadng]

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

[sdckapr]

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

Steve

[chipshot]

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.

[Michael Evans]

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

[sdckapr]

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>

[Michael Evans]

Yes Steve. Thank you.