Reverse Vlookup | Reverse Vlookup right to left | Reverse Vlookup right to left formula | CHOOSE Function | Reverse lookup example
Vlookup comes with limitations, vlookup can only return values from the right side. For example if you want to retrieve the data from left column of your lookup value you cannot retrieve that column using only vlookup.
To retrieve that column by vlookup you have to use trick or you have to use some other functions with vlookup to achieve it. In simple word there is no way to get reverse data by vlookup in standard configurations.
Let’s try to understand the issue by visualization. In below picture our requirement is material number against the Article number in requested data from exist data, but issue is material number is left to my lookup value and if we go through the standard configuration of vlookup like shows in below will not work.
Formula: =VLOOKUP(E3,A3:B17,2,FALSE)
To retrieve the material number against article number in required data from exist data we will use trick or we will use reverse vlookup to fulfill our requirement.
Table of Contents
HOW TO USE VLOOKUP IN REVERSE
To use vlookup in reverse or to get data from left column of lookup value we will use trick or we will use reverse vlookup. As we know there is not any standard configuration to get data from left column of lookup value by vlookup function so we will use choose function along with vlookup function to get data from left column of lookup value.
Here we are using vlookup function with choose function, Here E3 column is lookup value and for the table array we will use choose function by choose function we are creating index 1 and 2 to B3 to B17 1 and A3 to A17 2 and ‘2’ the column index number which we required in material number against article number and false is the value if value not found.
Formula: =VLOOKUP(E3,CHOOSE({1,2},$B$3:$B$17,$A$3:$A$17),2,FALSE)
Reverse Vlookup right to left
To understand the reverse vlookup we will seen the scenario via a simple example, we want to retrieve material number against the Article number from exist data into required data but due to required data is in left column of lookup value so standard configuration of vlookup will not work so here we will use reverse vlookup by using choose function in vlookup.
Reverse lookup example
Use vlookup function in material number column with choose function. In vlookup lookup value will be Article number column and for table array we will create table array by choose function and col_index_num is material number column which is required. Here E3 column is lookup value and for the table array we will use choose function by choose function we are creating index 1 and 2 to B3 to B17 is index 1 and A3 to A17 is index 2 and ‘2’ the column index number which we required in material number against article number and in table array we assigned material number index is 2 and false is the value if value not found.
Here we fixed the table array in choose function by F4 function key to assigned index of table array.
Also Look:
Dear Reader hope this article will help you to solve your issue. Please connect with use for this kind of articles.