We can use an array formula that is based on the **MMULT, TRANSPOSE, COLUMN,** and **INDEX** functions to lookup a value by matching across multiple columns. The steps below will walk through the process.

* Figure 1- How to Use INDEX and MATCH functions on Multiple Columns*

**General Formula**

**=INDEX(range1,MATCH(1,MMULT(--(range2=criteria),TRANSPOSE(COLUMN(range2)^0)),0))**

**Formula**

**=INDEX(Section,MATCH(1,MMULT(--(Names=G3),TRANSPOSE(COLUMN(Names)^0)),0))**

**Setting up the Data**

We will use the **INDEX** and **MATCH functions** to get the **Sections** where different workers are stationed as shown in** figure 2.**

*Figure 2 – Setting up the Data*

**Note: Cell B4** to **Cell B7** is a **named range** called “**Sections**.” To name this range, we will select **Cell B4 to Cell B7**. We will click on **B4** in **figure 3**. We will enter the name of the range as “Section” and press enter. We will do a similar thing for the **named range**, **C4:E7 as “Names”**

* Figure 3 – Naming the Ranges*

**Lookup Names with INDEX and MATCH on Multiple Columns **

- We will click on
**Cell H3** - We will insert the formula below into
**Cell H3**

**=INDEX(Section,MATCH(1,MMULT(--(Names=G3),TRANSPOSE(COLUMN(Names)^0)),0))** - Because this is an array formula, we will press
**CTRL+SHIFT+ENTER**

* Figure 4- Lookup Names with INDEX and MATCH functions on Multiple Columns*

- We will click on
**Cell H3**again - We will
**double click**on the fill handle tool which is the small plus sign you see at the bottom right of**Cell H3.**Select and drag down to copy the formula to**Cell H14**.

* Figure 5- Result of Lookup Names with INDEX and MATCH functions on Multiple Columns*

**Explanation**

The criteria in this formula is: **–(names=G3).** This criteria results in the generation of **TRUE** or **FALSE** results for the values in the data. The double negative forces the TRUE and FALSE values to produce 1 and 0 in an array like this:

**{1,0,0;0,0,0;0,0,0;0,0,0}**

This array is 4 rows by 3 columns, matching the structure of “Names”.

The **TRANSPOSE** section of the formula creates a second array in this form: **TRANSPOSE(COLUMN(Names)^0))**

The **COLUMN function** creates a numeric array having 3 columns and 1 row. The **TRANSPOSE FUNCTION** converts this array to 1 column and 3 rows. Raising to the power of zero simply converts all numbers in the array to 1. The MMULT function then performs matrix multiplication:

**MMULT({1,0,0;0,0,0;0,0,0;0,0,0},{1;1;1})**

The **MATCH function** receives the result from the** MMULT FUNCTION.** The lookup value of MATCH is **1.**

**MATCH(1,{1;0;0;0},0)**

The MATCH function returns the position of the first match, which corresponds to the row of the first matching row meeting the supplied criteria. This is feed into INDEX as the row number, with the named range “Section” as the array:

**=INDEX(Section,1)**

Finally,** INDEX** returns **“A.”**

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment