Diiicult to do without VBA ?

[keithkemble]

My apologies for not being able to paste using HTML maker

What I am trying to do, without using VBA, is to be able to identify the last stock code in column B relative to the Location and current date

EG dynamically, the last stockcode for location 3 before the 5th Feb is 51025
Current date is $C$1

I can sumproduct using two conditions to establish the total qty for 2 conditions. Can this be adapted to use find ?


A B C D E
Date stock code Doc ref Location Qty in/(out)
2-Feb-05 12345 A1234 1 130
2-Feb-05 456 A1235 2 130
2-Feb-05 51025 A1236 3 130
2-Feb-05 10035 A1237 floor 130
2-Feb-05 25025 A1238 goodsin 130
2-Feb-05 12345 Out654 1 -10
4-Feb-05 12345 Out655 1 -15
5-Feb-05 12345 Out656 1 -15
3-Feb-05 51025 Out657 3 -50
14-Feb-05 51025 Out658 3 -25
2-Feb-05 25025 Out659 5 -16

 

[fairwinds]

Hi,

Is this what you want?


H4: =LOOKUP(2,1/((A2:A12< H1)*(D2:D12=H2)),B2:B12)

Book1
	A	B	C	D	E	F	G	H
1	Date	stock	Doc	Location	Qty		Date	05-feb-05
2	02-feb-05	12345	A1234	1	130		Location	3
3	02-feb-05	456	A1235	2	130
4	02-feb-05	51025	A1236	3	130			51025
5	02-feb-05	10035	A1237	floor	130
6	02-feb-05	25025	A1238	goodsin	130
7	02-feb-05	12345	Out654	1	-10
8	04-feb-05	12345	Out655	1	-15
9	05-feb-05	12345	Out656	1	-15
10	03-feb-05	51025	Out657	3	-50
11	14-feb-05	51025	Out658	3	-25
12	02-feb-05	25025	Out659	5	-16
Sheet5

 

[keithkemble]

Fairwinds
Thats exactly what I want.
Works a treat. Thanks a million
Its obvious really but it takes a genius to point it out