Consolidating Data into one Search Box

[Smudge Smith]

Morning all. I've put together an excel report based upon Home Emergency claims in different postcode areas around the UK. I've designed a quick search tool using VLookup which allows me to input a postcode and then produces a result based upon different types of perils (Boilers, Internal Pipes, Toilets ect.) The issue I'm having is I've currently got a table of 13 different types of peril with each having a VLookup next to it in order to produce the result so the table looks a little untidy. What I've now tried to do is to consolidate the table into 3 search boxes, Peril, Postcode & Result. The Postcode & Result boxes are fine but I've really struggled to find a way of reducing these 13 Peril boxes into one which will allow me to choose any one Peril at a time then generate a result using the Postcode, any suggestions whether it be a Macro or any type of formula will be very much appreciated indeed so thank you in advance.

 

[shaowu459]

i'm not sure whether i understand you right, but do you mean this?

 

[Smudge Smith]

i'm not sure whether i understand you right, but do you mean this?
View attachment 14271

I've done this but the issue I'm having is then getting this to link somehow to the Postcode to get the Search Result for this particular peril.

 

[shaowu459]

I guess you need two-creteria lookup?

Book1
	A	B	C	D	E	F	G
1					Peril	Postcode	Search Result
2	Peril	Postcode	Search Result		Heating System	A	1
3	Toilet	C	3		Plumbing	B	2
4	Toilet	H	8		Toilet	C	3
5					Lost Keys	D	4
6					Other	E	5
7					Heating System	F	6
8					Plumbing	G	7
9					Toilet	H	8
10					Lost Keys	I	9
11					Other	J	10
Sheet1

Cell Formulas
Range	Formula
C3:C4	C3	=INDEX(G:G,MATCH(A3&B3,E:E&F:F,))

Cells with Data Validation
Cell	Allow	Criteria
A3:A4	List	=$I$2:$I$6

 

[Smudge Smith]

I guess you need two-creteria lookup?

Book1
	A	B	C	D	E	F	G
1					Peril	Postcode	Search Result
2	Peril	Postcode	Search Result		Heating System	A	1
3	Toilet	C	3		Plumbing	B	2
4	Toilet	H	8		Toilet	C	3
5					Lost Keys	D	4
6					Other	E	5
7					Heating System	F	6
8					Plumbing	G	7
9					Toilet	H	8
10					Lost Keys	I	9
11					Other	J	10
Sheet1

Cell Formulas
Range	Formula
C3:C4	C3	=INDEX(G:G,MATCH(A3&B3,E:E&F:F,))

Cells with Data Validation
Cell	Allow	Criteria
A3:A4	List	=$I$2:$I$6

Cheers for this, I'll see if I can get this to work for me as the other problem I've got is I've a total 124 different postcodes with thousands of claims which I need to interrogate so you can see why I require a tool to produce an instant result based simply upon selecting a peril and a postcode.

 

[shaowu459]

O

Cheers for this, I'll see if I can get this to work for me as the other problem I've got is I've a total 124 different postcodes with thousands of claims which I need to interrogate so you can see why I require a tool to produce an instant result based simply upon selecting a peril and a postcode.

OK, if you have further questions please let me know. upload sample data with expected results will help you resolve the problem more quickly.