**What are the INDEX and MATCH functions?**

The **INDEX** function is used to return a value at a given position in an array. This function can be used to retrieve individual values or entire rows and columns.

The **MATCH** function is used to get the relative position of an item/a lookup value in an array; a row, column, a list, or table.

**Formula**

**Generic formula for INDEX **Function:

**=INDEX (array,row_num,[col_num],[area_num])**

**Generic formula **for **MATCH **Function

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

**Explanation**

The **INDEX** is often used with the **MATCH** function to perform a two-way lookup where **MATCH** locates a value and feeds its position to **INDEX**.

In summary; **MATCH** finds the position of a value in a list while **INDEX** would get the value at that position.

**Syntax**

**=INDEX(data,MATCH(val_1,rows,1),MATCH(val_2,columns,1))**

**Example**

How to use the **INDEX** and **MATCH** functions to perform a Two-way lookup

In this example, our spreadsheet would be containing two tables; one is a multiplication table and the other is a table where we are supposed to calculate the product of the two numbers given on the table.

The following steps should be taken;

**Open**the existing spreadsheet

* Figure 1. Sample spreadsheet for Two way lookup with INDEX and MATCH*

**Click**on the**cell**where you would like the results of your two-way lookup to be displayed.

*Figure 2. Click on the cell*

**Insert**the following**formula**into the cell you have clicked to get the product of 9 and 10 from the table;**=INDEX(C6:G10,MATCH(J6,B6:B10,1),MATCH(J7,C5:G5,1))**

*Figure 3. Insert the formula into the cell*

- Press
**Enter**, the value retrieved and displayed should be**90.**

*Figure 4. Press ENTER to get the retrieved value*

**Insert**the following**formula**to get the product of 7 and 8 from the table;

then Press**=INDEX(C6:G10,MATCH(K6,B6:B10,1),MATCH(K7,C5:G5,1))****Enter.**

The image below displays our final results.

*Figure 5. Final Results Two Way lookup using INDEX and MATCH”*

**Notes**

How the formula, “

” worked: **=INDEX(C6:G10,MATCH(J6,B6:B10,1),MATCH(J7,C5:G5,1))**

picked the value in cell J6 (“9”), located its position (5) between row B6:B10 and fed this position to**MATCH(J6,B6:B10,1)****INDEX**.

picked the value in cell J7 (“10”), located its position (4) between column C5:G5 and fed this position to**MATCH(J7,C5:G5,1****)****INDEX**.**INDEX**took these rows and column positions of our array (row 5, column 4) and retrieved the value using those position to search through array**C6:G10**(which is the multiplication table).

## Leave a Comment