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