Vlookup, Index&Match,Sumproduct not working

[SteveWebb]

Hi,
I am struggling with a solution have tried several different possibilites.
Please see below an example:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Station Ground of Incident	Newcastle East	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
2	Month	2	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
3	Local Indicator	LI 4	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	Incident Count	2	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
10	Incident	Date and Time	Station ground of incident	Fire start location (primary fire only)	Igntion source (primary fire source)	Building number	Flat name number	Street	Locality	Ward	Property type level 3	Property type level 4	Property type level 5	False alarm reason 3	False alarm reason 4	Month	Quarter	Local Indicator
LookUp

#VALUE!

I am trying to return data as per the headings in A10:R10 from the user selected data in B1:B4.

The main data table being referenced is as follows:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Incident	Date and Time	Station ground of incident	Fire start location (primary fire only)	Igntion source (primary fire source)	Building number	Flat name number	Street	Locality	Ward	Property type level 3	Property type level 4	Property type level 5	False alarm reason 3	False alarm reason 4	Month	Quarter	Local Indicator
2	38006168	01/04/2010 02:20:47	Newcastle North	0	0	*	*	*	*	*	Dwelling	House - single occupancy	0	0	0	4	1	Not Found
3	38006169	01/04/2010 02:37:03	Newcastle East	Kitchen	Cooking appliance - Cooker incl. oven	*	*	*	*	*	Dwelling	Purpose Built Flat/Maisonette - single occupancy	Up to 3 storeys	0	0	4	1	LI 4
4	38006180	01/04/2010 04:30:42	Newcastle South	0	0	*	*	*	*	*	Non Residential	Food and Drink	Takeaway, fast food	Unknown	0	4	1	LI 22
5	38006183	01/04/2010 08:32:35	Newcastle North	0	0	*	*	*	*	*	Non Residential	Public Utilities	Electricity power station	Fire	Smoke/condensation	4	1	Not Found
6	38006185	01/04/2010 11:10:08	Newcastle South	0	0	*	*	*	*	*	Dwelling	House - single occupancy	0	By phone	0	4	1	LI 21
Data 2010f

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =COUNTIF($A$2:$A4896,A2)>1	Abc
B2	1. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))	Abc
A3	1. / Formula is =COUNTIF($A$2:$A4897,A3)>1	Abc
B3	1. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))	Abc
A4	1. / Formula is =COUNTIF($A$2:$A4897,A3)>1	Abc
B4	1. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))	Abc
A5	1. / Formula is =COUNTIF($A$2:$A4897,A3)>1	Abc
B5	1. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))	Abc
A6	1. / Formula is =COUNTIF($A$2:$A4897,A3)>1	Abc
B6	1. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))	Abc

Some of the data has been removed.
Whatever I try doesn't work, usually getting #Value or #N/A, I am trying to populate A11:R*** with the relevant data.

Seriously, any guidance will be well received by me probably in the form of alcohol.

Many thanks
Steve

 

[Matty]

Hi Steve,

If I'm reading this correctly, there wasn't an incident in Month 2 based on what you've posted of the Worksheet Data 2010f, so nothing would be returned.

If this is just because you've only posted a section of your data, could there be more than one incident based on what is chosen across B1, B2 and B3 in LookUp? I suspect there could be, hence you'd want all records returning in this case.

Let me know and I'll see if I can help...

Matty

 

[Matty]

If my assumptions were correct, try the following in A11 of LookUp:

=IFERROR(INDEX('Data 2010f'!$A$2:$R$6,SMALL(IF('Data 2010f'!$C$2:$C$6=$B$1,IF('Data 2010f'!$P$2:$P$6=$B$2,IF('Data 2010f'!$R$2:$R$6=$B$3,ROW('Data 2010f'!$A$2:$A$6)-ROW('Data 2010f'!$A$2)+1))),ROWS(A$11:A11)),MATCH(A$10,'Data 2010f'!$A$1:$R$1,0)),"")

Committed with CTRL+SHIFT+ENTER and copied across and down.

Expand ranges to suit your real data set.

Matty

 

[SteveWebb]

Hi Matty,

You are absolutely right on both counts.

There are incidents in month 2 (Feb 2011) they are in say A5790:R5800. Not posted.

Yes, I would expect anything up to say 200 incidents on the selected criteria ib B1:B3.

Thank you for taking time to look at my problem. Although worked based, I am working on this at home to improve my excel.

Cheers
Steve

 

[SteveWebb]

Hi Matty,

I am sat here thanking you out loud. The formula you supplied works.
I don't think that I would have got that now or in 2012. I will break it down to try and understand how it works.

5 days I have been at it and not got close.

Thank you
Steve

 

[Matty]

Glad it worked, Steve.

The formula's actually not that complicated when broken down. Post back if you want clarification about how it's working and I will try to explain.

Matty