Understanding Vlookup limitation
Typical or normal Vlookup formula can lookup value from left to right. Left to right Vlookup means it can give information about columns which are present right side of lookup column.
In the below image if we have to get student information using Student Id then Student Id is lookup value and Student Id column in the table is lookup column. By using normal Vlookup we can get all the columns information like Name, Birth Date, Blood Group since all columns are present right side of lookup column Student Id .
Blood group is obtained using normal Vlookup formula “=VLOOKUP(H2,$B$2:$E$6,4,FALSE)” present in cell H3.
But what if Blood Group column is present left side of Student Id column like below image. In this case we can only get Name and Birth Date information using normal Vlookup but not Blood Group. Since Blood Group column is left side of Student Id column.
One quick way for this problem is to cut paste Blood Group column right side of Student Id. But there also exists a formula solution for reverse Vlookup. Here I will discuss two ways of doing right to left Vlookup in Excel.
Right to left Vlookup using Index and Match function
First, we will calculate lookup value that is Student Id position in lookup column or range which is Student Id in table using Match function.
Match function used in cell H3 =MATCH(H2,$C$2:$C$6,0)
From below image you can see Match function has outputted 4 for student id value 3, which means 3 is at 4th position in Student Id range C2: C6
Now we know Student Id value 3 is at 4th position, don’t you think at same position its respective Blood Group value will be there in Blood Group range B2:B6.
Now to know at Nth place which value is present in a range we will use Index function. And this Index function will wrap Match function inside it for row value.
Now cell H3 formula will become =INDEX($B$2:$B$6,MATCH(H2,$C$2:$C$6,0),1) and this is outputting O+.
Index function syntax – Index(In a Range, at Nth row, in Nth column number)
Right to left Vlookup using Choose function
From the below image you can see formula used in cell H3 is =VLOOKUP(H2,CHOOSE({1,2},$C$2:$C$6,$B$2:$B$6),2,FALSE) .
In the above formula you can see all things are same as normal Vlookup function but at place of 2nd input choose formula is used.
Generally, in choose single index number is used.
CHOOSE(1,$C$2:$C$6,$B$2:$B$6 ) represents range $C$2:$C$6 which is Student Id and CHOOSE(2,$C$2:$C$6,$B$2:$B$6 ) represents range $B$2:$B$6 which is Blood Group.
But when you use {1,2} at index then it will combine Student Id and Blood Group range. You can say CHOOSE({1,2},$C$2:$C$6,$B$2:$B$6) internally represented as Student Id as first range and Blood Group range is second at right side of Student Id. So, you can say Choose formula is helping in reversing range order internally.
Learn more about embedding table or array values in Vlookup formula
In {“Student Id”,”Blood Group”;1,”A+”;2,”AB+”;3,”O+”;4,”O-“} comma “,” means new column and semi colon “;” means new row.
Just select CHOOSE({1,2},$C$2:$C$6,$B$2:$B$6) and press F9 to see array representation.
