Excel tutorials

Get Data Left to Right With INDEX/MATCH -
Easy Excel Example Tutorial

VLOOKUP Right to Left - Index/Match Functions, Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel, Use VLOOKUP to Lookup a Value to the Left, How do you Lookup Data to the Left?

These are all issues that many excel users pose when working in Excel. On this website in this tutorial, you can see an example of a solution with a visual view on screenshots.

The formula for searching data located on the left side of the column that contains the condition
We may use at least two types of formula with a combination of Excel functions INDEX, MATCH, VLOOKUP and CHOOSE.

In the previous tutorial, I showed how to use the VLOOKUP function, we can search the data range and return the result of a column which is located on the right side of the column in which the condition.

In this tutorial I will show how we can search the data ranges for a particular condition to return a result from a column located on the left side of the column in which the condition.

Example 1 - Search Data from the Left side Column used INDEX and MATCH Functions

In the first example of a combination of the INDEX and MATCH functions, we can create a formula that very well may be replaced by a formula that uses the VLOOKUP function, which you can see in the previous tutorial VLOOKUP formulas.

In the picture below you see the range of data and a condition that we search. The cell 'I2' is a condition, we searching the two columns.

The first column is the column where there is a a condition (in our case it is the column 'F').
The second column is the column where there is a desired result (in our case it is the column 'C').

Right to Left LOOKUP with Index Match Functions

  • Arguments of INDEX Function: Returns the intersection Row and Column from Array-Range of Data

=INDEX(array;row_num;column_num)

  • Arguments of MATCH Function: Result is Row number of Range Data

=MATCH(lookup_value;lookup_array;match_type)

 

The formula in cell 'J2' in the image above is as follows: Result is "apple" in Row 5 in range F2:F9 but return data from range C2:C9.
MATCH Function is Nested in the INDEX Formula/Function (Please see Tutorial Example number 53).

=INDEX(C2:C9;MATCH(I2;F2:F9;0))

MATCH function search a condition and returns the number of the order from a range of data in the 'F2:F9' for the condition in the 'I2' and this is number five (5). Furthermore INDEX function has arguments for Row and Column (we used Row argument only), and in this case the function searches the fifth (5) row in the range of data 'C2:C9' and returns it as a result of which the data or value as in this case the 'apple'. So this formula is the result back from the left side the conditions. If you want to see how this combination of functions can return the result from the right side column/conditions then refer to the previous tutorial VLOOKUP.

Example 2 - VLOOKUP to the Left in combination with the Choose function.

VLOOKUP to the Left.

In this second example I will show how we can use the CHOOSE function, which nest in the VLOOKUP function to return a result also from the left side of the column where there is a a condition.

The condition of the cell 'I2'.
The first column to search for a condition the column 'F' with a range of cells 'F2:F9'.
The second column from which formula should return the result is a column 'C' with a range of cells 'C2:C9'.

VLOOKUP to the left in Excel

Nested CHOOSE Function in VLOOKUP Formula

Arguments of VLOOKUP Function: Please, for details see basic example tutorial of Vlookup Function.

=VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

The formula in cell 'J2' in the image above is as follows:

=VLOOKUP(I2;CHOOSE({1\2};F2:F9;C2:C9);2;FALSE)

or

=VLOOKUP(I2;CHOOSE({1,2},F2:F9,C2:C9),2,FALSE)

Example formula is taken from the "author" of this fantastic formula, MTreacy. This formula could give me a layman translate, Search a condition in column 'F' and returns the result in column 'C'.