Multiple Results using VLOOKUP and INDEX + MATCH formulas

After the new SPILL feature in Excel, almost every Excel function is capable of returning results into multiple cells. In this article we will see, how the SPILL feature has made VLOOKUP and INDEX+MATCH formulas powerful than ever and why these formulas are still relevant even after the release of the XLOOKUP function.

SPILL in Excel

The term ‘SPILL’ Excel refers to “populating multiple cells using a single formula”. In the following examples, you can see how a single formula is spilling results into neighboring unoccupied cells.

Moreover, CSE syntax (CTRL+SHIFT+ENTER) is no longer required to create Array formulas. See how the TRANSPOSE (an array function) returns values into multiple cells without CSE Syntax.

The point is spilling feature has made almost every function more powerful than ever.

VLOOKUP / INDEX+MATCH to search for multiple Lookup values

In this section, we will see how to search for multiple lookup values using VLOOKUP and INDEX+MATCH formulas and return values into multiple cells

Here we have a table containing details of some Employees of a particular company.

To return the mail IDs of all the Employees listed in the range B3:B7, using a single VLOOKUP formula, use multiple lookup values (B3:B7) instead of a single lookup value as the first argument of VLOOKUP function.

=VLOOKUP(B3:B7,F3:H14,3,FALSE)

Argument 1: lookup_value – Value to search for. The Range of cells from B3 to B7 contains the values to search for. So, B3:B7 is the lookup_value.

Argument 2: table_array – Array containing the lookup values and the values to return. Range of cells from F3 to H14 is the table array.

Argument 3: col_ind_num – Index number of the column from which values are to be returned. Mail IDs are in the 3rd column. So, 3 is column index number.

Argument 4: range_lookup – FALSE for an Exact match

Even if, the same formula is displayed in every cell of the spill range, the actual formula resides in a single cell. In this case, formula resides in the cell C3 and any modification to the formula (Edit/Delete) is to be done in the cell C3.

Similarly, INDEX+MATCH combo can also be used to search for multiple lookup values.

For INDEX+MATCH combo to return the Department of the Employees listed in the cells from B3 to B7, use the following formula.

=INDEX(F3:I14,MATCH(B3:B7,F3:F14,0),4)

Argument 1 (INDEX Function): array – Array containing the lookup values and the values to return. The range of cells from F3 to I14 is the table array.

Argument 2 (INDEX Function): row_num – MATCH function will be used as the second argument to return the row number.

Argument 1 (MATCH Function): lookup_value – Value to search for. The range of cells from B3 to B7 contains the values to search for. So, B3:B7 is the lookup_value.

Argument 2 (MATCH Function): lookup_array – Array in which lookup values are searched for. The range of cells from F3:F14 contains the name of Employees. F3:F14 is the lookup_array.

Argument 3 (MATCH Function): match_type – 0 for an Exact match

Argument 3 (INDEX Function): col_num – Column from which values are returned. Details regarding the Department are in the 4^th column. So, 4 the is column number.

Multiple Lookup Values for Index Match Formula

VLOOKUP and INDEX+MATCH for returning values from multiple columns

In the previous examples, we have used multiple lookup values in VLOOKUP and INDEX+MATCH formulas. Here we will use a single lookup value and an array of column indexes to return results from different columns.

Another table containing the details of some Employees

The following formula returns the Name, Phone number, Mail ID and Department against the Employee ID in the cell B13

=VLOOKUP(B12,B3:F9,{2,3,4,5},FALSE)

Argument 1: lookup_value – The cell B12 which contain the Employee ID is the lookup_value

Argument 2: table_array – Range of cells from B3 to F9 is the table array.

Argument 3: col_ind_num – Index number of the column from which values are to be returned. For the VLOOKUP function to return, values from 2^nd, 3^rd, 4^th, and 5^th columns, those column numbers should be defined inside an array. {2,3,4,5} is column index number.

Argument 4: range_lookup – FALSE for an Exact match

When we use an array of values as the third argument, we have the freedom to choose the columns and their order. (With XLOOKUP function the return array should be an array of continuous columns)

To return the Department (5^th column) into the first cell (D12) and then Name (2^nd Column) into the second cell (E12), use the following formula