Vlookup, Index&Match,Sumproduct not working

SteveWebb

Board Regular
Joined
Feb 20, 2002
Messages
73
Hi,
I am struggling with a solution have tried several different possibilites.
Please see below an example:

Excel Workbook
ABCDEFGHIJKLMNOPQR
1Station Ground of IncidentNewcastle East****************
2Month2****************
3Local IndicatorLI 4****************
4******************
5******************
6Incident Count2****************
7******************
8******************
9******************
10IncidentDate and TimeStation ground of incidentFire start location (primary fire only)Igntion source (primary fire source)Building numberFlat name numberStreetLocalityWardProperty type level 3Property type level 4Property type level 5False alarm reason 3False alarm reason 4MonthQuarterLocal 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
ABCDEFGHIJKLMNOPQR
1IncidentDate and TimeStation ground of incidentFire start location (primary fire only)Igntion source (primary fire source)Building numberFlat name numberStreetLocalityWardProperty type level 3Property type level 4Property type level 5False alarm reason 3False alarm reason 4MonthQuarterLocal Indicator
23800616801/04/2010 02:20:47Newcastle North00*****DwellingHouse - single occupancy00041Not Found
33800616901/04/2010 02:37:03Newcastle EastKitchenCooking appliance - Cooker incl. oven*****DwellingPurpose Built Flat/Maisonette - single occupancyUp to 3 storeys0041LI 4
43800618001/04/2010 04:30:42Newcastle South00*****Non ResidentialFood and DrinkTakeaway, fast foodUnknown041LI 22
53800618301/04/2010 08:32:35Newcastle North00*****Non ResidentialPublic UtilitiesElectricity power stationFireSmoke/condensation41Not Found
63800618501/04/2010 11:10:08Newcastle South00*****DwellingHouse - single occupancy0By phone041LI 21
Data 2010f
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =COUNTIF($A$2:$A4896,A2)>1Abc
B21. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))Abc
A31. / Formula is =COUNTIF($A$2:$A4897,A3)>1Abc
B31. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))Abc
A41. / Formula is =COUNTIF($A$2:$A4897,A3)>1Abc
B41. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))Abc
A51. / Formula is =COUNTIF($A$2:$A4897,A3)>1Abc
B51. / Formula is =ISNUMBER(SEARCH("deliberate",$AI2))Abc
A61. / Formula is =COUNTIF($A$2:$A4897,A3)>1Abc
B61. / 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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
If my assumptions were correct, try the following in A11 of LookUp:

Code:
=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

Board Regular
Joined
Feb 20, 2002
Messages
73
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

Board Regular
Joined
Feb 20, 2002
Messages
73
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top