VLOOKUP Search in middle or right column & get result from left side column(s)

Objective

VLOOKUP always search in left column and get result from right side. How to make VLOOKUP search in right column and get result from left column.

Prerequisites

Good understanding of VLOOKUP, CHOOSE & Array.

Solution Concept

VLOOKUP will search always in 1) Left Column, 2) Top to Bottom. We will create a new table which will switch table columns, this new table will only be created in computer memory and hence will not appear on workbook. In this new table the column in which search is needed will be moved to left side.

Let’s do it

1.1.      Lets create a physical new table

1.1.1. CHOOSE is a simple function. It chooses nth item from a set of value. For example =CHOOSE(3,"Mango", "Orange", "Apple", "Banana") will give you result “Apple”.

1.1.2. In an ARRAY we get multiple results. Array equations are always entered by pressing “Shift”, “Ctrl” + “Enter” keys together instead of “Enter” key only.

1.1.3. We can manipulate table columns by using Choose and Array, wherein table columns are switched. To get multiple results we use {2,1} in first argument of CHOOSE; { & } reflect Array as usual.

1.1.4. Equation: =CHOOSE({2,1},C3:C8,D3:D8) ; Here, {2, 1} means move second values to first place and first values to second place. You should also notice in Value arguments, we have not used a single value but a RANGE (referring as column). Thus C & D columns switch their places. See Video:

1.2.   Implementing VLOOKUP without rearranged physical table but in computer memory

1.2.1.       Equation:=VLOOKUP(D15,CHOOSE({2,1},B15:B20,C15:C20),2,FALSE)

      Here we have only played with Range in standard VLOOKUP equation. Instead of giving a specific range, we have called CHOOSE & Array. Thus creating a new table in computer memory, which in turn giving us desired result.

Sample Workbook          

Prerequisites/See also (Links not activated yet)