In the previous post, we have seen the simple formula used to find the value based on descending order in Excel. In this tutorial, we will guide you to learn the simple steps to find the value in multiple columns by using the INDEX and MATCH functions in Excel. Let’s step them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
General Formula:
- To look up a value by matching across multiple columns, you can use the below array formula.
=INDEX(range1,MATCH(1,MMULT(–(range2=criteria),TRANSPOSE(COLUMN(range2)^0)),0))
Syntax Explanations:
- INDEX – The INDEX function returns the value at a given position in a range or array.
- MATCH – In Excel, this function will locate the position of a lookup value in a row, column, or table. Read more on the MATCH function.
- MMULT – This function helps to return the matrix product of two arrays. Read more on the MMULT Function.
- TRANSPOSE – The Excel TRANSPOSE function converts a vertical range to a horizontal range or a horizontal range to a vertical range.
- COLUMN – In Excel, the COLUMN function will return the column number for a reference
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Range – It represents the input ranges in your worksheet.
- Minus Operator (-) – This symbol will help to subtract any two values.
Example:
Refer to the below steps to get the matching value in multiple columns.
- First, we will give the input values in Column B to Column E.
- Then, we will enter the given formula in the formula bar section.
- After that, Press CTRL + SHIFT + ENTER Keys.
- It will display the result as per the below image.
Conclusion:
So yeah guys this is how you can get the matching value in multiple columns using the INDEX and MATCH function in Excel. Mention your queries in the comment box below. Thanks for visiting Geek Excel!! and Excel Formulas!!
Top comments (0)