Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Match Multiple Columns in Excel (5 Suitable Ways)

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.

excel match multiple columns


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 Sales. For any particular Fruits or Vegetables, you can use the following methods to match up other values corresponding to this Fruit or Vegetable from multiple columns.

dataset


Method-1: Use INDEX and MATCH functions on Multiple Columns

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.

INDEX MATCH excel match multiple columns

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 apply

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.

AutoFill

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

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.

Non-Array formula

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
For other versions except Microsoft 365, you must press 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

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.

excel match multiple columns

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


Method-4: Apply Array Formula to Match Multiple Criteria in Rows and Columns

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.

Insert Formula

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

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

VLOOKUP Function

➤Then, type the following formula

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

➤Then, press ENTER to get the output.

VLOOKUP Function excel match multiple columns

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
For other versions except Microsoft 365, you must press 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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

2 Comments
  1. 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

      Image of dataset

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

      Formula

      Output for 50 values:

      Output

      Note: If your dataset is very large kindly send us your dataset

      Thanks
      Shamima Sultana

Leave a reply

ExcelDemy
Logo