# Thread: Help with a Formula (Excel XP)

1. ## Help with a Formula (Excel XP)

I am creating a spreadsheet as below:-
A1 B1 C1 D1
Date Start End Total
1/1/03 2 4 5
2/1/03 4 2 8

In A1 I will enter a date, in column B1 I will enter 2 and in C1 I will enter 4. In D1 I want a formula that will look at the grid (below) and return 5. Does anyone know a formula that will do this for me?
1 2 3 4
1 0 3 3 7
2 3 0 3 8
3 3 3 0 5
4 7 5 5 0
The actual grid will be 1 -40 across and 1-40 down.

I have attached a copy of the question so you can see the grid and the above table in Word format.

2. ## Re: Help with a Formula (Excel XP)

<pre>=index(Table, B1,C1)</pre>

where Table is the range of the lookup table.

Steve

3. ## Re: Help with a Formula (Excel XP)

I am still having problems with the formula. I have attached a copy of the spreadsheet, if you could have a look at it and tell me what I need to do.
Thanks kindly.

4. ## Re: Help with a Formula (Excel XP)

You don't say what problems.

The actual lookup table is not F2:K7 but G3:K7, since row 2 and column F contain index numbers. So try this in D2:

=INDEX(\$G\$3:\$K\$7,B2,C2)

and fill down to D12.

5. ## Re: Help with a Formula (Excel XP)

Thank you very much for all you help, you have just saved me hours of work. I have been entering the totals manually for the last two years and it takes forever. I

6. ## Re: Help with a Formula (Excel XP)

I'm not sure I understand your question correctly, but I think you want this formula in D2, then fill down:

=INDEX(\$G\$3:\$M\$9,MATCH(B2,\$F\$3:\$F\$9,0),MATCH(C2,\$G \$2:\$M\$2,0))

See attached modified workbook.

7. ## Re: Help with a Formula (Excel XP)

Thank you for all your trouble, this will now save me hours of work. It's been great that I have gained a understanding of INDEX and MATCH function. One little thing though, I have been trying to work out the formula, but not clear on what the 0, at the end of the MATCH formula, represents.
[ =INDEX(\$G\$3:\$M\$9,MATCH(B2,\$F\$3:\$F\$9,0),MATCH(C2,\$G \$2:\$M\$2,0)) ].
Can I trouble you this last time for you to explain this? Thanking you again.

8. ## Re: Help with a Formula (Excel XP)

That's OK. The optional third argument in the MATCH function can be 1, 0 or -1.

If it is 1, the function looks for the greatest value in the lookup range that is less than or equal to the search value, and returns its index in the lookup range. This range *must* be sorted in ascending order. This is the default, if you omit the third argument, it is assumed to be 1.

If it is -1, the function looks for the smallest value in the lookup range that is greater than or equal to the search value, and returns its index in the lookup range. This range *must* be sorted in descending order.

If it is 0, the function looks for an exact match. The lookup range does not need to be sorted. This is the argument used in the formula for your workbook.

You can find this information, and more, in the online help. Type MATCH into the Answer Wizard or Help Index.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•