Sometimes you may want to transpose the values in an Excel spreadsheet and don’t know how to do that. In this situation, we are here to help you for transposing values from columns to rows based on multiple lookup criteria. We will assist you to understand the steps to achieve this task easily with the help of INDEX and MATCH functions. Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Syntax and its Explanation:
- You can use the INDEX function with the MATCH Function to look up the value with multiple criteria and then find the transpose of the result.
=INDEX($D$2:$D$10,MATCH(1,($B$2:$B$10=$F4)*($C$2:$C$10=G$3),0))
- INDEX function – This function returns the value at a given position in a range or array. INDEX is frequently used together with the MATCH function.
- MATCH function – It is used to locate the position of a lookup value in a row, column, or table.
Practical Example:
The following example will help you to understand how it works.
- You need to open the workbook where you need to apply this formula.
- Let us assume the example information of students and their marks scored in monthly tests.
- We are going to get the student marks based on a specific month ( Jan , May, Dec ).
- To do this, you need to place the cursor in the cell where you want to get the output value. We have placed the cursor in the active cell G4.
- Then apply the above-given formula and press the CTRL + SHIFT + Enter keys.
- You can drag the mouse from the cell G4 to I6 to fill the result as shown in the below screenshot.
Verdict:
In this tutorial, we have described the steps to transpose values from columns to rows based on multiple lookup criteria in Excel Office 365. Feel free to share your valuable queries and worthwhile feedback with us. Thanks for visiting Geek Excel!! Keep Learning!!
Top comments (0)