User Rating: 4 / 5     Left Lookup with Index, Match and Offset Excel Functions

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).