If you are looking for some of the easiest ways to match multiple columns in Excel then you might find this article beneficial. Sometimes it becomes necessary to search out a specific value in multiple columns in **Excel**. But doing this manually becomes tedious and inefficient. So, to do this you can follow any of the methods below for matching multiple columns in **Excel** easily.

Here is an overview of this article.

**Table of Contents**hide

**Download Excel Workbook**

**5 Suitable Ways to Match Multiple Columns in Excel**

Let’s look at the table below. Here, I have used 5 columns named ** Location, Year, Fruits, Vegetables, **and

**. For any particular**

*Sales***or**

*Fruits***you can use the following methods to match up other values corresponding to this**

*Vegetables,***Fruit**or

**Vegetable**from multiple columns.

__Method-1__**: Use INDEX and MATCH functions on Multiple Columns**

__Method-1__

Suppose, I want to get the** Sales **value corresponding to each item in the ** Products** column. To find this value you have to match up across multiple columns and use an

**Array formula**.

This formula includes the **COLUMN**, **TRANSPOSE**, **MMULT**, **MATCH**, and **INDEX** functions.

** Steps**:

➤Select the output **Cell G5.**

➤Type the following formula.

`=INDEX($D$5:$D$7,MATCH(1,MMULT(--($B$5:$C$7=F5),TRANSPOSE(COLUMN($B$5:$C$7)^0)),0))`

➤Then, press **ENTER** to get the output.

__Formula Breakdown__

**–($B$5:$C$7=F5) →**This will generate**TRUE/ FALSE**for every value in the range depending on the criteria whether it is met or not and then**—**will convert**TRUE**and**FALSE**into**1**and**0**.**Output:**{1,0;0,0;0,0}

**TRANSPOSE(COLUMN($B$5:$C$7)^0)****→****COLUMN function**will create an array with 2 columns and 1 row, and then**TRANSPOSE function**will convert this array to 1 column and 2 rows.**Power zero**will convert all of the values in the array to**1****Output:**{1;1}

**MATCH(1,MMULT(–($B$5:$C$7=F5),TRANSPOSE(COLUMN($B$5:$C$7)^0)),0)****→****The MMULT function**will perform matrix multiplication between these two arrays. This result will be used by**the MATCH function**as the**array argument**with lookup value**1**.**Output:**1

**INDEX($D$5:$D$7,MATCH(1,MMULT(–($B$5:$C$7=F5),TRANSPOSE(COLUMN($B$5:$C$7)^0)),0)) →**Finally, the**INDEX function**will return the corresponding value.**Output:**2000

➤After that, drag down the** Fill handle **to **AutoFill **the formula.

**Note**For other versions except** Microsoft 365**, you have to press **CTRL + SHIFT + ENTER ** instead of pressing **ENTER**.

**Read More:** **Match Two Columns and Output a Third in Excel (3 Quick Methods)**

__Method-2__**: Apply Array Formula to Match Multiple Criteria**

__Method-2__

For matching up multiple criteria in multiple columns and getting the value of **Sales**, you have to use an **Array formula **which includes **the INDEX** and** MATCH functions**.

** Steps**:

➤ First of all, select the output **Cell H7**

➤After that, write down the following formula in that cell.

`=INDEX(F5:F11, MATCH(1, (H4=B5:B11) * (H5=C5:C11) * (H6=D5:D11), 0))`

➤Then, press **ENTER** to get the output.

**Formula Breakdown**

**MATCH(1, (H4=B5:B11) * (H5=C5:C11) * (H6=D5:D11), 0)**→ Here,**1**is**lookup value**,**H4, H5, H6**are the criterion which will be looked up in**B5:B11, C5:C11,**and**D5:D11**ranges respectively and**0**is for an**exact match.****Output:**2

**INDEX(F5:F11, MATCH(1, (I4=B5:B11) * (I5=C5:C11) * (I6=D5:D11), 0))**→**The INDEX function**will give the corresponding value.**Output:**1000

**Note****CTRL + SHIFT + ENTER**instead of pressing

**ENTER**.

**Read More:** **Excel formula to compare two columns and return a value (5 examples)**

__Method-3__**: Use Non-Array Formula to Match Multiple Criteria**

__Method-3__

For matching up multiple criteria in multiple columns and getting the value of **Sales**, you can use a **Non-** **Array formula **which includes **the INDEX** and** MATCH functions**.

** Steps**:

➤The first step is to select the output **Cell H7.**

➤After that, type the following formula

`=INDEX(F5:F11, MATCH(1, INDEX((H4=B5:B11) * (H5=C5:C11) * (H6=D5:D11), 0, 1), 0))`

➤Then, press **ENTER** to get the output.

**Formula Breakdown**

**INDEX((I4=B5:B11) * (I5=C5:C11) * (I6=D5:D11), 0, 1)**→ This is the lookup_array for**the MATCH function**.**Output:**{0;1;0;0;0;0;0}

**INDEX(F5:F11, MATCH(1, INDEX((I4=B5:B11) * (I5=C5:C11) * (I6=D5:D11), 0, 1), 0))**→ This becomes,**INDEX(F5:F11, MATCH(1, {0;1;0;0;0;0;0}, 0))****Output:**1000

**Related Content:** **Compare Three Columns in Excel and Return a Value(4 Ways)**

**Similar Readings**

**How to Compare Two Columns in Excel for Missing Values (4 ways)****Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)****How to Compare Two Columns and Return Common Values in Excel****Excel Macro to Compare Two Columns (4 Easy Ways)****How to Compare Two Columns in Excel for Match (8 ways)**

__Method-4__**: Apply Array Formula to Match Multiple Criteria in Rows and Columns**

__Method-4__

Now, assume you want to match up criteria in both row-wise and column-wise. For doing this, you have to use an **Array formula **which includes **the INDEX** and** MATCH functions**.

__Steps:__

➤Firstly, select the output **Cell H8**

➤Secondly, type the following formula

`=INDEX(C6:E8, MATCH(H7,B6:B8,0), MATCH(H5&H6,C4:E4&C5:E5,0))`

➤Then, press **ENTER** to get the output.

__Formula Breakdown__

**MATCH(H7, B6:B8,0) →**This is used for row-wise matching.**Output:**2

**MATCH(H5&H6, C4:E4&C5:E5,0) →**This is used for column-wise matching.**Output:**1

**INDEX(C6:E8, MATCH(H7,B6:B8,0), MATCH(H5&H6,C4:E4&C5:E5,0)) →**This becomes,**INDEX(C6:E8, 2, 1,0)****Output:**1000

**Read More:** **How to Compare 3 Columns for Matches in Excel (4 Methods)**

__Method-5__**: Use VLOOKUP Function**

__Method-5__

Suppose, you want to know the corresponding **Year, Location,** and **Sales** value with respect to **Banana**. To get multiple values for one given data you have to use **the VLOOKUP function**.

** Steps**:

➤Select the 3 output cells simultaneously; **C10, D10, E10**

➤Then, type the following formula

`=VLOOKUP(B10,B4:E7,{2,3,4},FALSE)`

➤Then, press **ENTER** to get the output.

__Formula Explanation__

- Here,
**B10**is**lookup_value** **B4:E7**is the**table_array**,**{2,3,4}**is the**col_index_num****FALSE**is for**Exact match**.

**Note****CTRL + SHIFT + ENTER**instead of pressing

**ENTER**.

**Read More:** **How to Compare Three Columns in Excel Using VLOOKUP**

**Conclusion**

In this article, I tried to cover the easiest ways to match multiple columns in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.

First method does not work for larger sets of data.

Hi

Exceler,Here, I tried the first method for 50 sets of data and it worked but you have to change the cell references based on your dataset. For your better understanding, I am attaching the images along with the formula.

`=INDEX($D$5:$D$30,MATCH(1,MMULT(--($B$5:$C$30=F5),TRANSPOSE(COLUMN($B$5:$C$30)^0)),0))`

The images of datasets

Here, I used the formula for the entire dataset. I changed the references based on my dataset.

Output for

50values:Note:If your dataset is very large kindly send us your datasetThanks

Shamima Sultana