User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 
 
Left Lookup with Index, Match and Offset Excel Functions
 
Related Links:
1. Excel VLOOKP Function, with examples.
2. Left Lookup with VLookup Excel function
3. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.
4. Case Sensitive Vlookup; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value.
 

 
 
 
Left Lookup, with Index & Match functions
 
=INDEX($A$1:$D$10,MATCH(450,$B$1:$B$10,0),1)  [Formula]
 
The formula does a loookup of "450" in column B and returns corresponding value in column A, which is "Laurel". (Refer Table 1)
 
Returns the value (Laurel) in an array ($A$1:$D$10), at row_num 5 (5 is returned by the below MATCH function) and column_num 1 (ie. column A).
 
=MATCH(450,$B$1:$B$10,0)  [Formula Break]
 
This part of the formula returns the value 5, which is the relative position of Lookup_value (ie. 450) in the lookup_array ($B$1:$B$10).
 
------------------------------------------------------------------------------------------------------------------
 

Left Lookup, with Offset & Match functions
 
=OFFSET($A$1:$D$10,MATCH(C4,OFFSET($A$1:$D$10,0,2,ROWS($A$1:$D$10),1),0)-1,0,1,1)  [Formula]
 
The formula does a loookup of C4 in column C and returns corresponding value in column A, which is "Juliet". (Refer Table 2) 
 
The value of 3 (returned from MATCH function below), is used for "rows" in this OFFSET formula.
 
Formula Offsets 3 rows down from A1, in same column A (cols = 0), single cell (height, width being 1) ie. A4, and returns value in cell A4, which is "Juliet".
 
=ROWS($A$1:$D$10)  [Formula Break]
 
This part of the formula returns the value 10, which is the number of rows in a reference or array. Syntax: ROWS(array).
 
=OFFSET($A$1:$D$10,0,2,ROWS($A$1:$D$10),1)  [Formula Break]
 
This part of the formula returns one column (width = 1) with 10 rows (height = 10), starting 2 columns (cols = 2) to the right of column A (viz column C).
 
The returned column C1:C10, is the one in which Lookup value C4 is present.
 
=MATCH(C4,OFFSET($A$1:$D$10,0,2,ROWS($A$1:$D$10),1),0)-1  [Formula Break]
 
Match Function returns the value 3, which is the position of C4 in Column C1:C10 (4).
 
We deduct 1 from the match result of 4, for use in OFFSET function (4 minus 1 = 3).