Apart from **VLOOKUP**, we can **lookup items in excel using a combined formula of INDEX and MATCH** functions. This is more popular than **VLOOKUP**. **MATCH FUNCTION** returns the numeric position of an item in a list. **INDEX FUNCTION** returns the **VALUE** at a known position. The steps below will walk through the process.

*Figure 1- Lookup with INDEX and MATCH functions*

**Formula**

**=INDEX(C4:E13,MATCH(H3,B4:B13,0),MATCH(H4,C3:E3,0))**

**Setting up the Data**

We will use the **INDEX** and **MATCH functions** to get the **February Sales** for **New York** **Branch** of a Pharmacy **Store** with **Branches** across the **US**

- The
**Branches**of the store will be entered into**Column B** **Column C, D,**and**E**will contain the**Sales**for 3 consecutive months- The result will be returned in
**Cell H5**

* Figure 2 – Setting up the Data*

**Lookup New York February Sales**

- We will click on
**Cell H5** - We will insert the formula below into
**Cell H5**

**=INDEX(C4:E13,MATCH(H3,B4:B13,0),MATCH(H4,C3:E3,0))** - We will press the
**enter**key

* Figure 3- New York February Sales with the INDEX and MATCH Functions*

**Explanation**

**=INDEX(C4:E13,MATCH(H3,B4:B13,0),MATCH(H4,C3:E3,0))**

`=INDEX(array,row_num,column_num)`

**Row number:****Match(lookup_value,lookup_array,match_type)**

**Column Number:**`(Match(lookup_value,lookup_array,match_type))`

In this formula, the **INDEX FUNCTION** is used to return the **value **within an array at the specified **row number** and **column number** by the **MATCH FUNCTION**. The returned **row** to **INDEX** is 4 and the **column i**s 2.

**=INDEX(C4:E13,4,2)**

The** INDEX function **will check the range** (C4:E13) **and return the value in row 4 and column 2.

The first **MATCH function** gets the **Row number** of New York in the range **(B4:B13) **and returns as **4**. The second **MATCH function** gets the **Column number** for the Month (February Sales) in the range **(C3:E3)** and returns as **2**. This is then returned to INDEX which delivers the result.

**0 **in the formula means that we want an exact matching value.

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment