Results 1 to 8 of 8
Thread: Help with a Formula (Excel XP)

20031209, 11:10 #1
 Join Date
 Jul 2002
 Location
 melbourne, Victoria, Australia
 Posts
 61
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 140 down.
I have attached a copy of the question so you can see the grid and the above table in Word format.

20031209, 11:40 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Help with a Formula (Excel XP)
<pre>=index(Table, B1,C1)</pre>
where Table is the range of the lookup table.
Steve

20031210, 00:16 #3
 Join Date
 Jul 2002
 Location
 melbourne, Victoria, Australia
 Posts
 61
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031210, 00:33 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20031210, 02:40 #5
 Join Date
 Jul 2002
 Location
 melbourne, Victoria, Australia
 Posts
 61
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031210, 08:50 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20031211, 00:05 #7
 Join Date
 Jul 2002
 Location
 melbourne, Victoria, Australia
 Posts
 61
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031211, 00:14 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.