For advanced lookups in Excel, the **INDEX **and the **MATCH **can be used instead of **VLOOKUP**. **INDEX **and **MATCH **are required for extensive lookups and a lot of them are absolutely amazing. With example, we’ll teach you how to utilize the Excel **INDEX **and **MATCH **functions in Excel in this tutorial.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 8 Effective Examples of the INDEX-MATCH Formula in Excel

We’ll use an example data set in the sections below to demonstrate how to utilize **the INDEX and the MATCH functions** on it. We’ll show you how to apply both functions in 8 different ways to do a lookup for values.

**♣Syntax:**

=**INDEX** (*array*, *row_num*, [*column_num*])

**♣Arguments:**

**array**: the range of cells of values**Row_num**: returns results for a row in the selected range.**column_num**: returns results for a column in the selected range.

**♣Result:**

The **Index Function **returns the value or reference of the cell in a specified range that is at the intersection of a specific row and column.

**♣Syntax:**

=**MATCH**(*lookup_value*,*lookup_array*,[*match_type*])

**♣Arguments:**

**lookup_value**: The value you want to find in range.**lookup_array**: The range where you are searching the**lookup_value**.**match_type**: Specifies how lookup value and lookup array values are matched in Excel.**1**= exact or next smallest.**0**= exact match.**-1**= exact or next largest.

**♣Result:**

Returns the position of an item in an array in terms of its relative position.

### Example 1: Basic Combination of the INDEX and MATCH Functions in Excel

Let’s say, you want to do a lookup the **Price **for a particular **Order ID**. We’ll combine the **INDEX **and the **MATCH **function to do so.

**Step 1:**

- In cell
**G5**, type the following formula.

`=INDEX(D5:D11, MATCH(G4,B5:B11,0))`

Here,

**MATCH(G4,B5:B11,0)**refers to cell**G4**as the**lookup_value**in the range**B5:B11**for an exact match. It returns**4**as the value is in row number**4**.**INDEX(D5:D11, MATCH(G4,B5:B11,0))**refers**D5:D11**as an array from where we get the value and**row_num**is**4**as we got it from the**MATCH****Column_num**is not required as we apply**INDEX**for only one column.

**Step 2:**

- Press
**Enter**to the result.

**Read More:** **Examples with INDEX-MATCH Formula in Excel (8 Approaches)**

### Example 2: Use the INDEX-MATCH Formula to Lookup from the Left in Excel

You may also do a lookup from one column to the other. The value for column **C**, the second column of our **INDEX **range **B5:D11**, will be found in the following example. Follow the steps below to perform a lookup from the left.

**Step 1:**

- Type the following formula in cell
**G5**.

`=INDEX($B$5:$D$11,MATCH(F5,$B$5:$B$11,0),2)`

**Step 2:**

- Then, press
**Enter**to see the result.

**Step 3:**

- Finally, use
**AutoFill**to see results in full.

**Read More:** **How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results**

### Example 3: Analyze Case-sensitive Condition by Combining INDEX and MATCH Functions in Excel

For **Case-sensitive analysis**, you can apply the **INDEX **and the **MATCH **function in combination.

To analyze and do a lookup, follow the steps below.

**Step 1:**

- We want to find the ID of
**MARK**instead of**Mark**. Let’s see what happens. Enter the following formula cell**C5**.

`=INDEX(B5:B11,MATCH(TRUE,EXACT(G4,C5:C11),0))`

**Step 2:**

- Finally, press
**Enter**to find the**Order ID**for**MARK**.

Look closely; the result for the perfect match with the capital letter **MARK **was obtained, but not for the **Mark**.

### Example 4: Lookup Two Columns with INDEX-MATCH Formula in Excel

It’s fascinating to note that you may do a lookup in multiple columns for **multiple criteria** at the same time. For example, we’ll look for a person’s **Order ID **as well as the **Price**. Follow the steps below to perform a lookup for two criteria.

**Step 1:**

- Firstly, type the following formula in cell
**G5**.

`=INDEX(B5:B11,MATCH(TRUE,EXACT(G4,C5:C11),0))`

- Secondly, press
**Enter**to get the first value in cell**G5**.

Therefore, you will the first lookup value in cell **G5**.

**Step 2:**

- To get the second lookup value, type the following formula in cell
**G6**.

`=INDEX(D5:D11,MATCH(G4&G5,C5:C11&B5:B11,0))`

- This is an array function so we need to apply this function by pressing
**Crtl**+**Shift**+**Enter**

- As a result, you will have the first lookup value in cell
**G6**.

**Read More:** **INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)**

**Similar Readings**

**Excel INDEX MATCH to Return Multiple Values in One Cell****Excel INDEX-MATCH Formula to Return Multiple Values Horizontally****[Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)****How to Select Specific Data in Excel (6 Methods)****INDEX MATCH vs VLOOKUP Function (9 Examples)**

### Example 5: Two-Way Lookup Using INDEX-MATCH Formula

You can execute a lookup for a row and a column in **Two-way**. Follow the steps below to find a value in a row and column at the same time.

**Step 1:**

- Enter the formula in cell
**G7**.

`=INDEX(B4:D11,MATCH(G5,B4:B11,0),MATCH(G4,B4:D4,0))`

**Step 2:**

- Finally, press
**Enter**.

### Example 6: Find the Closest Match in Excel by Merging INDEX and MATCH Functions

The **INDEX **function and the **MATCH **function are useful for finding or seeking the closest match between ranges. For example, we would want to determine who has the closest value to our target value, which is **$15** in this case. To do so, simply follow the steps below.

**Step 1:**

- To find the closest match, type the following formula.

`=INDEX(C5:C11,MATCH(MIN(ABS(D5:D11-G4)),ABS(D5:D11-G4),0))`

Here,

**ABS(D5:D11-G4)** is the absolute value of difference with target value **15 **between the range **D5:D11**.

**MIN(ABS(D5:D11-G4)** commands to find the minimum difference of the absolute values.

**Step 2:**

- By pressing
**Ctrl**+**Shift**+**Enter**, we will apply this function as an array function.

**Read More:** **INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)**

### Example 7: Three-Way Lookup Using the INDEX and MATCH Functions in Excel

A **Three-way Lookup** is the finest thing you can accomplish using the **INDEX **and the **MATCH **function. **Three-way Lookup **adds an additional dimension of relevance. For example, we can figure out what Jenny paid for two different items in three separate months. Follow the easy steps outlined below to get it done.

**Step 1:**

- To perform
**Three-way Lookup**, at first type the formula below in cell**H6**.

`=INDEX(($D$5:$E$7,$D$10:$E$12,$D$15:$E$17),MATCH($G$6,$C$5:$C$7,0),MATCH(H$5,$D$4:$E$4,0),(IF(H$4="Jan",1,IF(H$4="Feb",2,3))))`

**Step 2:**

- Finally, press
**Enter**to get the first lookup value.

**Step 3:**

- Then, copy the formula for other cells by using the
**AutoFill**

Therefore, you will obtain the results as shown in the below image.

### Example 8: Apply the INDEX-MATCH Formula with Wildcard Characters

Excel can also determine the **partial match** value. The **INDEX **and **MATCH **functions are used to do this. To accomplish so, we’ll use an asterisk **(*) **as a wildcard character. Simply follow the instructions outlined below to get started.

**Step 1:**

- Firstly, in cell
**G5**, type the formula below.

`=INDEX($D$5:$D$11,MATCH(F5&"*",$C$5:$C$11,0),1)`

**Step 2:**

- Then, press
**Enter**button to see the changes.

**Step 3:**

- Just use the
**AutoFill**tool to fill the required cells.

As a consequence, you can see that it returns a result for “** Jenny**,” even though it isn’t exactly matched.

**Read More:** **INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)**

## Conclusion

To sum up, I hope this article has demonstrated how to utilize the **INDEX **and **MATCH **functions to integrate multiple criteria. Look over the practice book and put what you’ve learned to use. We are willing to reimburse programs like this because of your support.

If you have any questions, please do not hesitate to contact us. Please let me know what you think in the comments section below.

Your questions will be answered as soon as possible by the **ExcelDemy **professionals.

## Related Articles

**INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)****Index Function to Match and Return Multiple Values Vertically in Excel****INDEX MATCH across Multiple Sheets in Excel (With Alternative)****Index Match Multiple Criteria in Rows and Columns in Excel****INDEX MATCH Multiple Criteria in Excel (Without Array Formula)****Excel Index Match single/multiple criteria with single/multiple results**