Left lookup in excel (Index & match function)

Vlookup is the most popular formula among the lookup formulas in excel, however it has some draw-back like it will lookup most left column data and return result from right column. But there are situations when user need to lookup values from any column and get result from left column.

For that Index and match functions can be used together to lookup data from any column.

First time you may find this formula complicated but when you get used to it. Then it will be most useful and easiest formula for you.

How it works:

Index: Returns the value of an element in a table or an array, selected by the row and column number indexes.

Syntax:

=INDEX (array, row_num, [column_num])

array: table range
row_num: select the row number from which to return value.
column_num: select the column number from which to return value.

 Instead of static number of row and column here match function can be used to get dynamic numbers.

Match: returns the position of the matched value within lookup_array, not the value itself.

Syntax:

= MATCH (lookup_value, lookup_array, [match_type])

lookup_value: The value that you want to match in lookup_array.

lookup_array: The range of cells being searched.

match_type: The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

  

Example:

 Below example have sales data in which order details are in second column. There is another table where only ‘order id’ given from that product and amount details required. ‘Product’ is most left column and ‘amount’ is most right, to get this data Index and match functions can be used as powerful formula. This combination of formula can get data of any cell from the array you just need to be careful while giving range and cell references.

Once you insert this formula in first cell just copy it in next column and below rows you will get ‘product’ and ‘amount’ details corresponding of ‘order id’ given.

Please use comment section to provide your feedback or to ask questions.