Choose function also returns the table based on the position of the index number.The IF function will return the table when we supply the TRUE (1) and FALSE (0) values in the logical argument in curly brackets.With the help of the IF and CHOOSE functions, we can retrieve the values from the left column of the lookup value column in the VLOOKUP function.In these situations, we need to employ different methods to get the result from the desired column. This is the primary limitation of the VLOOKUP to the left function. Step 3: Choose the table array from B2:D7, because our starting column should be the lookup column i.e., the Product ID column.įrom the current selection of the table array, the category column is not even part of it because it is to the left of the lookup value column, i.e., Product ID. Step 2: Choose the lookup value as cell G2. Step 1: Enter the VLOOKUP function in cell H2. The steps used to obtain results using VLOOKUP to the left are as follows: We will try to fetch sales and category information using VLOOKUP to the left. In this article, let us learn the limitations of the VLOOKUP to the left function and some of the alternative methods to overcome the limitations of the VLOOKUP function in Excel.įirst, let us understand the limitation of the VLOOKUP function when the data is organized in a way which is not suitable to apply VLOOKUP function.įor example, consider the below table showing the product IDs of fruits along with the respective sales.

Any columns located to the left of the VLOOKUP function cannot be retrieved by the VLOOKUP to the left function. We need to arrange the dataset in such a way that the lookup value column is located at the extreme left and all the columns displaying the results are located to the right of the lookup value column to make VLOOKUP work. It is one of the limitations of the VLOOKUP function. In order to address this limitation, you can use IF and CHOOSE functions, to retrieve the values from the left column of the lookup value column in the VLOOKUP function. However, users should know that VLOOKUP function can work only from left to right and not from right to left.

VLOOKUP function retrieves the values from the vertical dataset.

How to use vlookup