# looking up previous value in column

#### mfleuette

##### New Member
Need some creative insight -

Simple checkbook application:
ColA = Amount
ColB = Account
ColC = Balance (computed field = previous balance - current amount)

For a row curr (curr>1), I want to:
1.look up the previous occurrence of the account in B(curr) (i.e. where B(curr) = B(prev) where prev=Max(1 --> n-1)),
2. get the previous balance from C(prev),
3. subtract the current amount A(curr) from C(prev), and
4. store it in the current balance C(curr)

The interesting part (I think) is finding the PREVIOUS occurrence of the account in ColB, not the FIRST occurrence (e.g. by using VLOOKUP)

Anyone have any thoughts on how to do this?

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
A 10-row sample along with the desired result would help.

Hi
If you separate each account to a different sheet then use column B for income and column C for expenditure then you can do running totals for each colum and take the difference as your current balance
HTH
Bob

OK, in re a sample:

DESC AMT ACCT BAL
A B C D
CASH 10.00 <-- orig balance
CHECK 100.00 <-- orig balance
apple .30 CASH 9.70 <-- computed by subtracting .30 from 10.00
dinner 10.00 CHECK 90.00 <-- computed by subtracting 10 from 100
soda 1.00 CASH 8.70 <-- computed by subtracting 1 from 9.70

Maybe a simpler question would be how do you find the last occurrence of an item in a range?

mfleuette said:
OK, in re a sample:

DESC AMT ACCT BAL
A B C D
CASH 10.00<-- orig balance
CHECK 100.00<-- orig balance
apple .30 CASH 9.70<-- computed by subtracting .30 from 10.00
dinner 10.00 CHECK 90.00<-- computed by subtracting 10 from 100
soda 1.00 CASH 8.70<-- computed by subtracting 1 from 9.70

Maybe a simpler question would be how do you find the last occurrence of an item in a range?
Book2
ABCD
1DESCAMTACCTBAL
2CASH10
3CHECK100
4apple0.3CASH9.7
5dinner10CHECK90
6soda1CASH8.7
7
Sheet1

D4, copied down:

=LOOKUP(2,1/(\$C\$2:C3=C4),\$D\$2:D3)-B4

Brilliant! Many thanks -

Replies
2
Views
84
Replies
2
Views
129
Replies
3
Views
91
Replies
3
Views
567
Replies
1
Views
232

1,203,642
Messages
6,056,511
Members
444,871
Latest member
Vishal Gupta

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back