Excel Formulas: Multi-criteria Lookup and Transpose!!
Tags: excelformula, excelformulas
Published: 2021-01-20 11:52:19

---
title: Excel Formulas: Multi-criteria Lookup and Transpose!!
published: true
date: 2021-01-20 11:46:57 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-multi-criteria-lookup-and-transpose/
---

If you want to perform a **multi-criteria lookup and transpose** results into a table, you can use an array formula based on the **INDEX** and **MATCH** functions. Let’s see them below!! Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

[![Multi-Criteria Lookup and Transpose](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Multi-Criteria-Lookup-and-Traspose.png)](https://geekexcel.com/excel-formulas-multi-criteria-lookup-and-transpose/multi-criteria-lookup-and-traspose/#main)<figcaption id="caption-attachment-21261">Multi-Criteria Lookup and Transpose</figcaption>
## Generic Formula:

- Use the below formula to get the transpose results.

**=[INDEX](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)(range1,[MATCH](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)(1,([$A1](https://geekexcel.com/use-absolute-reference-in-excel-office-365-with-examples/)=range2)\*(B$1=range3),0))**

## Syntax Explanations:

- **INDEX** – In Excel, the **[INDEX function](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)** will help to return the value at a given position in a range or array.
- **MATCH** – The [**MATCH function**](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)in Excel is used to locate the position of a lookup value in a row, column, or table.
- **Absolute Reference ($)** – The **[absolute reference](https://geekexcel.com/use-absolute-reference-in-excel-office-365-with-examples/)** is an actual fixed location in a worksheet.
- **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 data given in the worksheet.
- **A1, B1** – It represents the criteria.
- **Multiplication (\*)** – In this symbol will multiply any two values or numbers.

## Practical Example:

Please do as follows to look up the table with transpose results. Refer to the below example image.

- First, we will enter the input values in **Column**  **B** , **Column**  **C** , **Column**  **D**.

![Input Range](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Input-Range-11.png)<figcaption id="caption-attachment-21262">Input Range</figcaption>

- Now, we are going to sort out the table as shown below and look up the values based on the given criteria.

![Criteria](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/To-Lookup.png)<figcaption id="caption-attachment-21263">Criteria</figcaption>

- Enter the given formula to cell G3 or formula bar section.

![Enter the formula](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Enter-the-formula-17.png)<figcaption id="caption-attachment-21264">Enter the formula</figcaption>

- After that, Press **CTRL** + **SHIFT** + **ENTER**  Keys.
- Finally, we will get the results as per the below image.

![Result](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Result-37.png)<figcaption id="caption-attachment-21265">Result</figcaption>

## Wind-Up:

Hope you understood the simple steps to find out the **multi-criteria lookup and transpose the results in Excel**. Please feel free to state your **query** or **feedback** for the above article. To learn more, check out **[Geek Excel](https://geekexcel.com/)!!** And **[Excel Formulas](https://geekexcel.com/excel-formula/)!!**

### Read Also:

- **[Excel Formulas to Lookup the Entire Column ~ Simple Guide!!](https://geekexcel.com/excel-formulas-lookup-entire-column/)**
- **[Transpose Values Based on Multiple Lookup Criteria in Excel Office 365!!](https://geekexcel.com/transpose-values-based-on-multiple-lookup-criteria-in-excel-office-365/)**