Lookup table/Index

[brin]

Hi Guys

I have set up a dropdown box that allows me to select for 4 criteria-Construction, age, size,condition ( see Yellow coloured headings).

Under the heading-Construction there are four selections- Double Brick, Brick Veneer,Weatherboard,Fibrous Cement.

The selection that I have made from all of the lists is shown in Green:

Double brick

1960-1999

120-200m2

Good

<tbody>
</tbody>

I want to lookup the added value from the schedule. The correct answer is $1000-$1299.

I assume that I need a Lookup Table/Index or something, but I have no real idea and will rely on you experts to help me out.

Please assume that I know nothing (which is probably the case) and explain it clearly or better still give me the formula that I can use.

Thankyou in anticipation



Lookup

	A	B	C	D	E
1	Construction:	Age:	Size:	Condition:	Range:
2	Double Brick	1960-1999	120-200m2	Good	$1000-$1299
3	Double Brick	2000-current	>300m2	Poor
4	Brick Veneer	1960-1999	200-300m2	Fair
5	Weatherboard	1945-1960	120-200m2	Good
6	Fibrous Cement	1930-1945	<120m2	As New
7		Pre 1930

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4





Lookup

Lookup

	G	H	I	J	K	L	M	N
3	Construction	Age			Condition
4	Size	Poor	Fair	Good	As new
5	Double Brick	2000-current	>300m2	$300-$599	$600-$899	$900-$1249	$1250-$1700
6	1960-1999	$250-$549	$550-$849	$850-$1149	$1150-$1650
7	1945-1960	$200-$499	$500-$799	$800-$1100	$1100-$1600
8	1930-1945	$150-$449	$450-$749	$750-$1049	$1050-$1550
9	Pre 1930	$100-$399	$449-$699	$700-$999	$1000-$1500
10	2000-current	200-300m2	$350-$649	$650-$949	$950-$1299	$1300-$1750
11	1960-1999	$300-$599	$600-$899	$900-$1249	$1250-$1700
12	1945-1960	$250-$549	$550-$849	$850-$1199	$1200-$1650
13	1930-1945	$200-$499	$500-$799	$800-$1149	$1150-$1600
14	Pre 1930	$150-$449	$450-$749	$750-$1099	$1100-$1550
15	2000-current	120-200m2	$450-$749	$750-$1049	$1050-$1349	$1350-$1850
16	1960-1999	$400-$699	$700-$999	$1000-$1299	$1300-$1800
17	1945-1960	$350-$649	$650-$949	$950-$1249	$1250-$1750
18	1930-1945	$300-$599	$600-$899	$900-$1199	$1200-$1700
19	Pre 1930	$250-$549	$550-$849	$850-$1149	$1150-$1650
20	2000-current	<120m2	$550-$849	$850-$1149	$1150-$1449	$1500-$1950
21	1960-1999	$500-$799	$800-$1099	$1100-$1399	$1400-$1900
22	1945-1960	$450-$749	$750-$1049	$1050-$1349	$1350-$1850
23	1930-1945	$400-$699	$700-$999	$1000-$1299	$1300-$1800
24	Pre 1930	$350-$649	$650-$949	$950-$1249	$1250-$1750

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:124.67px;"><col style="width:128px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:86px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4





Excel tables to the web >> Excel Jeanie HTML 4







Excel tables to the web >> Excel Jeanie HTML 4

 

[brin]

Hi
I have added the data to this spreadsheet and it works most of the time, but there are combinations that don't work.
Maybe it is a formatting issue or something like that?

Sheet1

	A	B	C	D	E
1	Double Brick	1945-1960	200-300m2	Poor	#N/A

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 {=INDEX(Sheet2!$E$1:$E$320,MATCH($A1&$B1&$C1&$D1,Sheet2!$A$1:$A$320&Sheet2!$B$1:$B$320&Sheet2!$C$1:$C$320&Sheet2!$D$1:$D$320,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

An example of the data on sheet 2 is:
Sheet2

	A	B	C	D	E
1	Double Brick	>2000	>300m2	Poor	$300-$599
2	Double Brick	1960-1999	>300m2	Poor	$250-$549
3	Double Brick	1945-1960	>300m2	Poor	$200-$499
4	Double Brick	1930-1945	>300m2	Poor	$150-$449
5	Double Brick	Pre 1930	>300m2	Poor	$100-$399
6	Double Brick	>2000	200-300m2	Poor	$350-$649
7	Double Brick	1960-1999	200-300m2	Poor	$300-$599
8	Double Brick	1945-1960	200-300m2	Poor	$250-$549
9	Double Brick	1930-1945	200-300m2	Poor	$200-$499

<tbody>
</tbody>

The YELLOW part is on the table but doesn't calculate in the formula.

Excel tables to the web >> Excel Jeanie HTML 4


Excel tables to the web >> Excel Jeanie HTML 4

 

[Legacy 143009]

It is working for me again. Sorry. May be a formatting issue. Beware of using misleading or mistrailing spaces in the text.

Data validation is great for this. Check the sample below:
Sample1.xlsx

 

[brin]

Thanks
Your Sample works well. so there is no problem with the formula.
The probem is with the data that I have added.
I have cheded spaces etc and can't find where the problem is.
It works with most search combinations, but some don't.
Not sure whethe I am allowed to attach a sample file as the table is very long?

 

[Legacy 143009]

It is not recommended but if you have to you may attach.

 

[brin]

I have prepared a cut down sample vesrion of my spreadsheet that I want to attach but can't find the icon or whatever I neeed to do this.

 

[Legacy 143009]

I have prepared a cut down sample vesrion of my spreadsheet that I want to attach but can't find the icon or whatever I neeed to do this.

You should upload to cloud (e.g OneDrive, DropBox) and share the link.

 

[brin]

I have attached a sample file which is a cut down version of the original.
The first 5 columns (A to E) are the table where the data is being matched from.
This data has been taken from Columns F to L (the original data table that I created).
Shown in yellow on sheet 2 is one example that doesn’t work.
The Data validation list is also on Sheet 2.

https://1drv.ms/x/s!AoCzBqIBpNdFhdEHJ10eLqvzkO38bg

 

[brin]

I think I have resolved the problem.
I have recopied some of the data and made sure that it is correct in the table and in the Data Validation List.
This worked, so I will have to tediously copy the data across.
It looks like I need to copy as values and not just do a copy and paste.

 

[Legacy 143009]

I think I have resolved the problem.
I have recopied some of the data and made sure that it is correct in the table and in the Data Validation List.
This worked, so I will have to tediously copy the data across.
It looks like I need to copy as values and not just do a copy and paste.

Ahh yess ofcourse. It is always better to paste only values if you dont need the formula or formatting. Because the normal paste comes with the whole formatting including unwanted spaces, etc...

Nice to hear that you've solved the problem Have a nice day

 

[brin]

Just when I thought I had solved the problem -I hadn’t.
I have changed data around and checked typing etc and I still have problems with it.
Seems to be just a date problem.
The sample link that I posted is still representative of the problem.