Vlookup version 2

J

Jon

This has been bugging us for ages. We have a solution that works on on
page, but not across pages.

Does anyone have a "super" vlookup that allows you to lookup values t
the left of a column,

e.g. =vlookup2(Target as range or Text, lookuprange as range, value (
or 1 = same column, -1 is column first to left, 2 is column first t
right), true/false)

oui / no
 
C

Charles Williams

Hi Jon,

You can use VLOOKUP embedded in OFFSET or INDEX and MATCH

=OFFSET(VLOOKUP(Target,LookupRangeColumn,1,True/false),0,-1)
where -1 is the column to the left, 0 is the lookupcolumn itself, 1 is the
column to the right

=INDEX(TheRange,MATCH(Target,LookupRangeColumn,-1/0/1),ColNo)
where colno is the column number within the range

The OFFSET solution takes less memory but is volatile, so I generally use
the INDEX approach

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com
 
Top