Combine the Index and Match formulas in Microsoft Excel

» Combine the Index and Match formulas in Microsoft Excel

VERSIONS: All Microsoft Excel Versions

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)

Screenshot // Combine the Index and Match formulas in Microsoft Excel

Rate this tip



1	2	3	4	5

RATING: 2.93
VIEWS: 60263

Email Tip

Print Tip

Add to My Tips

Ms-Excel Balance Sheets

Asif Zahid wrote on December 31, 1969 19:00 EST

Dear Sir/Madam
I would like to submit my request that how can we calculate the given % Profit and % Loss in our sheet.Please give me some tips for this
Thanks very much and hope to hear from you soon
Yours Sincerely
Asif

Ms-Excel Balance Sheets

Asif Zahid wrote on December 31, 1969 19:00 EST

Please let me know that how can I calculate the %Profit & %Loss in a given amount.
Hope to hear from you soon
Asif

formula

sxu57 wrote on December 31, 1969 19:00 EST

Dear Sir Madam
How can I write formula to find the largest number in the one column along with the corresponding name in the other column but same row?

Yours Sincerely
sxu57

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.