In Column B I have Sales, Column C Sales Tax and Column D the anticipated
deposit (combination of the two). These columns are broken down for daily
totals in rows 4 thru 34 (1-Jan-08 thru 31-Jan-08).

Further down in the sheet I want to be able to enter into Column D (say row
37) the actual deposit and have cells B37 and C37 populate with the matching
Sales and Sales Tax amount for that deposit

Do you want to enter one of the values in Col D and then get the
corresponding values from Col B & C?

If that is the requirement then why not use Data-Filter and pick the value
you want?

If you HAVE to do it your way then
=MATCH(D37,D34:C15,0) will give you the row no. for deposit corresponding to
the value in D37
You can then use OFFSET to get the value from B & C

Here is the complete setup
Enter the value you want to lookup in D37
Enter in B37
=OFFSET(B1,(MATCH($D$37,$D$1:$D$34,0))-1,0,1,1)
Enter in C37
=OFFSET(C1,(MATCH($D$37,$D$1:$D$34,0))-1,0,1,1)

Note: This will return the first match only... There is no error checking...

"handyaccountant" wrote:

In Column B I have Sales, Column C Sales Tax and Column D the anticipated
deposit (combination of the two). These columns are broken down for daily
totals in rows 4 thru 34 (1-Jan-08 thru 31-Jan-08).

Further down in the sheet I want to be able to enter into Column D (say row
37) the actual deposit and have cells B37 and C37 populate with the matching
Sales and Sales Tax amount for that deposit

Thank you for the attempt but it didn't return anything of use.
Here is a sample of what I am working with:
A B C D
Sales Sales Tax Deposit
1/1/08 29.50 1.43 30.93
1/2/08 300.00 8.25 308.25

This table is being pulled from another sheet with all of the transactions
and totalling it for the day using the date on the left. The goal for this
formula is to be able to go down the bank statement entering only the deposit
(using here the deposit for the 2nd). Let's say the deposit cleared the bank
on the 5th, although the credit card company shows it as the 2nd. I would
like to be able to enter:
A B C D
1/5/08 308.25

And have the formula return:

A B C D
1/5/08 300.00 8.25 308.25

Of course I need to do this for hundreds of deposits within a given month,
making a formula much more desirable.

Thanks
handya

"Sheeloo" wrote:

Do you want to enter one of the values in Col D and then get the
corresponding values from Col B & C?

If that is the requirement then why not use Data-Filter and pick the value
you want?

If you HAVE to do it your way then
=MATCH(D37,D34:C15,0) will give you the row no. for deposit corresponding to
the value in D37
You can then use OFFSET to get the value from B & C

Here is the complete setup
Enter the value you want to lookup in D37
Enter in B37
=OFFSET(B1,(MATCH($D$37,$D$1:$D$34,0))-1,0,1,1)
Enter in C37
=OFFSET(C1,(MATCH($D$37,$D$1:$D$34,0))-1,0,1,1)

Note: This will return the first match only... There is no error checking...

"handyaccountant" wrote:

In Column B I have Sales, Column C Sales Tax and Column D the anticipated
deposit (combination of the two). These columns are broken down for daily
totals in rows 4 thru 34 (1-Jan-08 thru 31-Jan-08).

Further down in the sheet I want to be able to enter into Column D (say row
37) the actual deposit and have cells B37 and C37 populate with the matching
Sales and Sales Tax amount for that deposit