Tip Printed from ExcelTip.com

---

Combine the Index and Match formulas in Microsoft Excel


Step 1: Define a name
Define a Name to column B ( see screen shot ). In the example, the Name is ColB.

Step 2: Enter the Match formula

• Select a new sheet. In cell A1, enter one text from the expenses list.
• Calculate the row number. In cell B1, enter the formula =MATCH (A1, ColB, 0). The result of the calculation is 9.
• In cell A2, enter the date 4/1/2001.
• Calculate the column number. In cell B2, enter the formula =MATCH (A2, Row1, 0). The result of the calculation is 7.

Step 3: Enter the Index formula

• In cell C1, enter the formula =INDEX(Data, B1, B2). The result is 345.

Step 4: Combine (nesting) the formulas
Now you will use the technique of copying and pasting a formula from the formula bar (without the = sign) into a different formula.

• From the formula bar of cell B1, copy the Match formula without the = (equal) sign.
• Click the X to the left of the formula (to cancel).
• Select the C1 cell; in the formula bar, select the B1 address; and press Ctrl+V.
• Use the same technique to copy the Match formula from the B2 cell to the formula bar in cell C1 (instead of the B2 address).

The combined formula is :
=INDEX(DATA, MATCH(A1, ColB, 0), MATCH(A2, Row 1,0)