Vlookup

[simonw]

Hi:

Given a block of data the vlookup function will find the first occurrence that meets the given criteria: Is there a method to find the 2nd, 3rd 4th etc row of data that also meets the original criteria?

Example criteria: coating events on 4/9/06

Date Area Total
4/9/06 proc 150
4/9/06 coating 300
4/9/06 coating 150
4/9/06 coating 250


The vlookup function will find 4/9/06 coating 300 but miss the other two entries that meet the criteria in this example.
I cannot use the function Sumif as I need to see the events, and I would preferred not to use pivot tables or macros.

Many thanks

 

[Peter_SSs]

simonw

Is this what you want? Formula in F3 is:
=SUMPRODUCT(--(A2:A5=F1),--(B2:B5=F2),C2:C5)

If not, post back with more details.

Mr Excel.xls
	A	B	C	D	E	F	G
1	Date	Area	Total		Date	04/09/2006
2	04/09/2006	proc	150		Area	Coating
3	04/09/2006	coating	300		Result	700
4	04/09/2006	coating	150
5	04/09/2006	coating	250
6
Sumproduct

 

[simonw]

Peter:

Many thanks your solution is operating

Regards

simonw