» Combine the Index and Match formulas in Microsoft Excel
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
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.
- In cell C1, enter the formula =INDEX(Data, B1, B2). The result is 345.
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).
=INDEX(DATA, MATCH(A1, ColB, 0), MATCH(A2, Row 1,0)
Book Store:
Recommended Books:
- Business Analysis with Microsoft Excel (2nd Edition)
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- Understanding Financial Statements
- How to Use Financial Statements: A Guide to Understanding the Numbers
- How to Read A Financial Report
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
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
Does that solve your problem?
Alan.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
time cards at work. Can I do this useing excel?