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.

Vlookup with Column function

In this post you not only learn Vlookup you will also learn how to use a column function to avoid manual efforts.
Many people use Vlookup in day to day work, if it’s used in one column, then no issue, but if using it in more than one column, then the changing column index number is manual task which most of people don’t like.

When you copy and paste formula to the next column, then column index number remains the same it never changes like other values (range/cell reference). To make the column index number dynamic we can use “Column” function.

First will see what Vlookup is?

This function looks at the first column of an array and moves across the row to return the value of a cell. In other words, it will find the specified values or relevant information from the array (the input or source table).

Syntax:

=Vlookup (lookup_value, table_array, col_index_num, [range_lookup])

Detailed breakdown of the syntax:

lookup_value: the values which you want to lookup (eg: “Text” or A1).

table_array: the source data table from where you want to lookup (eg: $A$1: $D$5).

col_index_num: Third argument is column index number, number of column in array from the lookup values column.(eg: if looking up data from Column A to get details from Column C then the Index number will be ‘3’).

In this argument we will use a column function to make column index dynamic.

[range_lookup]: even though it’s an optional argument, it plays a very important role to get the correct data, this argument is where we define exact or approx match by selecting true/false or 1/0.
For exact match use 0 and for approx use 1.

How it works:

In below example there are two tables one is working table where we are filling data using vlookup function and the other one is source table from where we are looking up data.

As you see if I write the formula in fist cell and drag it throughout the table then column index number won’t change, it remains the same number “2”. ''=VLOOKUP ($A4, $H$3:$L$12, 2, 0)”

Now will use the column function to make this column index dynamic so you just need to write formula once and drag it throughout the table and you will get correct data.

As given in above table I replaced column index by column function.

What Column function do?

Returns the column number of the given cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.).

Syntax:

=Column ([Reference])

If you write only column function without any reference it will how that columns index number
eg: write formula in B2 cell “=Column()” the 2 value will appear

Reference: it nothing but a cell reference eg: in B2 cell write “=Column(D1)” formula and now 4 value will appear.

Detail function of this formula given below.

How Column function works: Column function will write index number of column you mention if you look at in above table in cell B3 the formula is “=VLOOKUP($A3,$A$17:$E$26,COLUMN(B1),0)”. In column I took “B1” as reference instead of column index “2”

Column function will show the index number like for A1= 1, B3=2, C6=3….. Z1=26 here row number doesn’t affect only the column reference matters.

To check whether you selected right column reference use “F9” function key select the column function and press “F9” function key “=VLOOKUP ($A3, $A$17:$E$26, COLUMN(B1), 0)” it will show the outcome of that formula as “=VLOOKUP ($A3, $A$17:$E$26, {2}, 0)”

Note: While using Vlookup make sure you are applying correct cell references.