# lookup item number with operation

#### lochem

##### New Member
Hi,
Ive seen similar posts but cant find what i need to do:

i have a database with many fields, "item number" is in column A and "amount in stock" is column N.

item numbers are repeated often in column A and are unsorted.
for each item appearance, i need to deduct -1 into "amount in stock" from the last time any item appeared.
i need the code to perform the following:
For example the result should be:

Item Num Amount in Stock
10802 21
10301 20
10203 17
10301 19
10502 15
10203 16

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Andrew Poulsom

##### MrExcel MVP
Try:

=IF(COUNTIF(A\$1:A2,A2)=1,B2,INDEX(C\$1:C1,MATCH(A2,A\$1:A1,FALSE))-1)

in C2 copied down.

#### lochem

##### New Member
hmm.. can you please explain your function? so i can appropriately apply it where necessary

thanks!

#### Andrew Poulsom

##### MrExcel MVP
If A2 is the fist occurrence of itself in column A, B2 is used, otherwise the first matching value above is used, with 1 deducted.

Actually, i just reread your post. My formula won't work for more than 2 occurences.

#### Andrew Poulsom

##### MrExcel MVP
For multiple occurrences try:

=IF(COUNTIF(A\$1:A2,A2)=1,B2,LOOKUP(2,1/(A\$1:A1=A2),C\$1:C1)-1)

#### lochem

##### New Member
thanks, ill give this a go and see how it works out.

Replies
12
Views
2K
Replies
28
Views
415
Replies
5
Views
511
Replies
3
Views
673
Replies
2
Views
1K

1,136,968
Messages
5,678,866
Members
419,787
Latest member
juanam

### 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