|

# » 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) Screenshot // Combine the Index and Match formulas in Microsoft Excel

Rate This Tip
 1 2 3 4 5
Rating: 2.54     Views: 99809
Military time
Beckie  Posted on: 31-12-1969
I want to canculate Military time to regular time to figure
time cards at work. Can I do this useing excel?
Reply: Military time - Beckie from Norman, Ok wrote on September 1, 2003 11:19 AM EST
Alan  Posted on: 31-12-1969
Hi Beckie,

Yes - Excel can do that very easily.

If you want specific details, please post back with a specific example of what you need.

Alan.
military time
Beckie  Posted on: 31-12-1969
example: I would like to be able to key a time card into an excel worksheet and compute the time for the week without useing a ten key.. started at 7:05 am went to lunch at 12:15-12:45 went home at 18:30 ...

thanks
beckie
Reply: military time - Beckie from Norman Ok wrote on September 3, 2003 5:39 PM EST
Alan  Posted on: 31-12-1969
Hi Beckie,

Just set ujp your worksheet as it makes sense, then subtract the starting time from the finishing time in each 'shift'.

The result will be the hours that you worked, and they can be added up to give you the total hours.

Watch your formatting - what you seein a cell, may not be exactly what you are expecting. For example, if you format a cell to show just hours:mins, and the total is 25 hours, 12 mins, it will display as 1:12 (being the hours and mins left over after taking out the days, which your format asked excel to exclude).

If you want to show hours past 24, use this format:

[hh]:mm