Reverse/backward VLOOKUP advance excel function

Reverse or Backward Vlookup or lookup.

For understanding reverse or backward VLOOKUP let’s get clear about forward / direct VLOOKUP function here : /articles/excel-function-vlookup-21678.asp

So I assume you are clear about syntax of vlookup, basically to lookup any value from table there should be 2 ranges, one range containing values to be looked up & second range containing result you need to get.

Have a look at following example sheet; there is 1st column with employee ID, then name, location, cell number of employee.

Now you have cell number of employee and you want to search name of employee! In this case our traditional vlookup function does not work, so how to get result with out adding any additional column.

Ok, let’s work it out together, as I said earlier for vlookup to work we need at least two ranges which we already have, so lets tweak vlookup unction to get desired result.

We will keep original syntax except we will tweak range portion & we will use choose function of excel, so your new syntax is

VLOOKUP(lookup_value, CHOOSE({1,2},rng_1,rng_2), col_index_num, match_type)

Here lookup_value is value is the value to search for in the first column of the table_array, in our case it is value of cell E2,

rng_1  is range containing cell no. as we are searching cell no. to get name, in our case it is range D2:D9 ,

rng_2  is range containing name as we are searching name, in our case it is range B2:B9 ,

col_index_num is the column number in table_array from which the matching value must be returned. The first column is 1, for 2nd it is 2 & so on, in our case it is 2.

Now if you wish to search Location instead of name then you need to change address of range_2, i.e. instead of range B2:B9 give range C2:C9

Excel file attached for your practice.

Do post feedback & querries.