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

 

[Legacy 143009]

If I were you I would start to make a table in the sense of more like a SQL support table since your date is four-ish dimensional.
I prepared a ready table for you. Just fill the prices then use it

Double Brick	Pre 1930	<120m2	As New
Double Brick	Pre 1930	<120m2	Good
Double Brick	Pre 1930	<120m2	Fair
Double Brick	Pre 1930	<120m2	Poor
Double Brick	Pre 1930	120-200m2	As New
Double Brick	Pre 1930	120-200m2	Good
Double Brick	Pre 1930	120-200m2	Fair
Double Brick	Pre 1930	120-200m2	Poor
Double Brick	Pre 1930	200-300m2	As New
Double Brick	Pre 1930	200-300m2	Good
Double Brick	Pre 1930	200-300m2	Fair
Double Brick	Pre 1930	200-300m2	Poor
Double Brick	Pre 1930	>300m2	As New
Double Brick	Pre 1930	>300m2	Good
Double Brick	Pre 1930	>300m2	Fair
Double Brick	Pre 1930	>300m2	Poor
Double Brick	1930-1945	<120m2	As New
Double Brick	1930-1945	<120m2	Good
Double Brick	1930-1945	<120m2	Fair
Double Brick	1930-1945	<120m2	Poor
Double Brick	1930-1945	120-200m2	As New
Double Brick	1930-1945	120-200m2	Good
Double Brick	1930-1945	120-200m2	Fair
Double Brick	1930-1945	120-200m2	Poor
Double Brick	1930-1945	200-300m2	As New
Double Brick	1930-1945	200-300m2	Good
Double Brick	1930-1945	200-300m2	Fair
Double Brick	1930-1945	200-300m2	Poor
Double Brick	1930-1945	>300m2	As New
Double Brick	1930-1945	>300m2	Good
Double Brick	1930-1945	>300m2	Fair
Double Brick	1930-1945	>300m2	Poor
Double Brick	1945-1960	<120m2	As New
Double Brick	1945-1960	<120m2	Good
Double Brick	1945-1960	<120m2	Fair
Double Brick	1945-1960	<120m2	Poor
Double Brick	1945-1960	120-200m2	As New
Double Brick	1945-1960	120-200m2	Good
Double Brick	1945-1960	120-200m2	Fair
Double Brick	1945-1960	120-200m2	Poor
Double Brick	1945-1960	200-300m2	As New
Double Brick	1945-1960	200-300m2	Good
Double Brick	1945-1960	200-300m2	Fair
Double Brick	1945-1960	200-300m2	Poor
Double Brick	1945-1960	>300m2	As New
Double Brick	1945-1960	>300m2	Good
Double Brick	1945-1960	>300m2	Fair
Double Brick	1945-1960	>300m2	Poor
Double Brick	1960-1999	<120m2	As New
Double Brick	1960-1999	<120m2	Good
Double Brick	1960-1999	<120m2	Fair
Double Brick	1960-1999	<120m2	Poor
Double Brick	1960-1999	120-200m2	As New
Double Brick	1960-1999	120-200m2	Good
Double Brick	1960-1999	120-200m2	Fair
Double Brick	1960-1999	120-200m2	Poor
Double Brick	1960-1999	200-300m2	As New
Double Brick	1960-1999	200-300m2	Good
Double Brick	1960-1999	200-300m2	Fair
Double Brick	1960-1999	200-300m2	Poor
Double Brick	1960-1999	>300m2	As New
Double Brick	1960-1999	>300m2	Good
Double Brick	1960-1999	>300m2	Fair
Double Brick	1960-1999	>300m2	Poor
Double Brick	2000-current	<120m2	As New
Double Brick	2000-current	<120m2	Good
Double Brick	2000-current	<120m2	Fair
Double Brick	2000-current	<120m2	Poor
Double Brick	2000-current	120-200m2	As New
Double Brick	2000-current	120-200m2	Good
Double Brick	2000-current	120-200m2	Fair
Double Brick	2000-current	120-200m2	Poor
Double Brick	2000-current	200-300m2	As New
Double Brick	2000-current	200-300m2	Good
Double Brick	2000-current	200-300m2	Fair
Double Brick	2000-current	200-300m2	Poor
Double Brick	2000-current	>300m2	As New
Double Brick	2000-current	>300m2	Good
Double Brick	2000-current	>300m2	Fair
Double Brick	2000-current	>300m2	Poor
Brick Veneer	Pre 1930	<120m2	As New
Brick Veneer	Pre 1930	<120m2	Good
Brick Veneer	Pre 1930	<120m2	Fair
Brick Veneer	Pre 1930	<120m2	Poor
Brick Veneer	Pre 1930	120-200m2	As New
Brick Veneer	Pre 1930	120-200m2	Good
Brick Veneer	Pre 1930	120-200m2	Fair
Brick Veneer	Pre 1930	120-200m2	Poor
Brick Veneer	Pre 1930	200-300m2	As New
Brick Veneer	Pre 1930	200-300m2	Good
Brick Veneer	Pre 1930	200-300m2	Fair
Brick Veneer	Pre 1930	200-300m2	Poor
Brick Veneer	Pre 1930	>300m2	As New
Brick Veneer	Pre 1930	>300m2	Good
Brick Veneer	Pre 1930	>300m2	Fair
Brick Veneer	Pre 1930	>300m2	Poor
Brick Veneer	1930-1945	<120m2	As New
Brick Veneer	1930-1945	<120m2	Good
Brick Veneer	1930-1945	<120m2	Fair
Brick Veneer	1930-1945	<120m2	Poor
Brick Veneer	1930-1945	120-200m2	As New
Brick Veneer	1930-1945	120-200m2	Good
Brick Veneer	1930-1945	120-200m2	Fair
Brick Veneer	1930-1945	120-200m2	Poor
Brick Veneer	1930-1945	200-300m2	As New
Brick Veneer	1930-1945	200-300m2	Good
Brick Veneer	1930-1945	200-300m2	Fair
Brick Veneer	1930-1945	200-300m2	Poor
Brick Veneer	1930-1945	>300m2	As New
Brick Veneer	1930-1945	>300m2	Good
Brick Veneer	1930-1945	>300m2	Fair
Brick Veneer	1930-1945	>300m2	Poor
Brick Veneer	1945-1960	<120m2	As New
Brick Veneer	1945-1960	<120m2	Good
Brick Veneer	1945-1960	<120m2	Fair
Brick Veneer	1945-1960	<120m2	Poor
Brick Veneer	1945-1960	120-200m2	As New
Brick Veneer	1945-1960	120-200m2	Good
Brick Veneer	1945-1960	120-200m2	Fair
Brick Veneer	1945-1960	120-200m2	Poor
Brick Veneer	1945-1960	200-300m2	As New
Brick Veneer	1945-1960	200-300m2	Good
Brick Veneer	1945-1960	200-300m2	Fair
Brick Veneer	1945-1960	200-300m2	Poor
Brick Veneer	1945-1960	>300m2	As New
Brick Veneer	1945-1960	>300m2	Good
Brick Veneer	1945-1960	>300m2	Fair
Brick Veneer	1945-1960	>300m2	Poor
Brick Veneer	1960-1999	<120m2	As New
Brick Veneer	1960-1999	<120m2	Good
Brick Veneer	1960-1999	<120m2	Fair
Brick Veneer	1960-1999	<120m2	Poor
Brick Veneer	1960-1999	120-200m2	As New
Brick Veneer	1960-1999	120-200m2	Good
Brick Veneer	1960-1999	120-200m2	Fair
Brick Veneer	1960-1999	120-200m2	Poor
Brick Veneer	1960-1999	200-300m2	As New
Brick Veneer	1960-1999	200-300m2	Good
Brick Veneer	1960-1999	200-300m2	Fair
Brick Veneer	1960-1999	200-300m2	Poor
Brick Veneer	1960-1999	>300m2	As New
Brick Veneer	1960-1999	>300m2	Good
Brick Veneer	1960-1999	>300m2	Fair
Brick Veneer	1960-1999	>300m2	Poor
Brick Veneer	2000-current	<120m2	As New
Brick Veneer	2000-current	<120m2	Good
Brick Veneer	2000-current	<120m2	Fair
Brick Veneer	2000-current	<120m2	Poor
Brick Veneer	2000-current	120-200m2	As New
Brick Veneer	2000-current	120-200m2	Good
Brick Veneer	2000-current	120-200m2	Fair
Brick Veneer	2000-current	120-200m2	Poor
Brick Veneer	2000-current	200-300m2	As New
Brick Veneer	2000-current	200-300m2	Good
Brick Veneer	2000-current	200-300m2	Fair
Brick Veneer	2000-current	200-300m2	Poor
Brick Veneer	2000-current	>300m2	As New
Brick Veneer	2000-current	>300m2	Good
Brick Veneer	2000-current	>300m2	Fair
Brick Veneer	2000-current	>300m2	Poor
Weatherboard	Pre 1930	<120m2	As New
Weatherboard	Pre 1930	<120m2	Good
Weatherboard	Pre 1930	<120m2	Fair
Weatherboard	Pre 1930	<120m2	Poor
Weatherboard	Pre 1930	120-200m2	As New
Weatherboard	Pre 1930	120-200m2	Good
Weatherboard	Pre 1930	120-200m2	Fair
Weatherboard	Pre 1930	120-200m2	Poor
Weatherboard	Pre 1930	200-300m2	As New
Weatherboard	Pre 1930	200-300m2	Good
Weatherboard	Pre 1930	200-300m2	Fair
Weatherboard	Pre 1930	200-300m2	Poor
Weatherboard	Pre 1930	>300m2	As New
Weatherboard	Pre 1930	>300m2	Good
Weatherboard	Pre 1930	>300m2	Fair
Weatherboard	Pre 1930	>300m2	Poor
Weatherboard	1930-1945	<120m2	As New
Weatherboard	1930-1945	<120m2	Good
Weatherboard	1930-1945	<120m2	Fair
Weatherboard	1930-1945	<120m2	Poor
Weatherboard	1930-1945	120-200m2	As New
Weatherboard	1930-1945	120-200m2	Good
Weatherboard	1930-1945	120-200m2	Fair
Weatherboard	1930-1945	120-200m2	Poor
Weatherboard	1930-1945	200-300m2	As New
Weatherboard	1930-1945	200-300m2	Good
Weatherboard	1930-1945	200-300m2	Fair
Weatherboard	1930-1945	200-300m2	Poor
Weatherboard	1930-1945	>300m2	As New
Weatherboard	1930-1945	>300m2	Good
Weatherboard	1930-1945	>300m2	Fair
Weatherboard	1930-1945	>300m2	Poor
Weatherboard	1945-1960	<120m2	As New
Weatherboard	1945-1960	<120m2	Good
Weatherboard	1945-1960	<120m2	Fair
Weatherboard	1945-1960	<120m2	Poor
Weatherboard	1945-1960	120-200m2	As New
Weatherboard	1945-1960	120-200m2	Good
Weatherboard	1945-1960	120-200m2	Fair
Weatherboard	1945-1960	120-200m2	Poor
Weatherboard	1945-1960	200-300m2	As New
Weatherboard	1945-1960	200-300m2	Good
Weatherboard	1945-1960	200-300m2	Fair
Weatherboard	1945-1960	200-300m2	Poor
Weatherboard	1945-1960	>300m2	As New
Weatherboard	1945-1960	>300m2	Good
Weatherboard	1945-1960	>300m2	Fair
Weatherboard	1945-1960	>300m2	Poor
Weatherboard	1960-1999	<120m2	As New
Weatherboard	1960-1999	<120m2	Good
Weatherboard	1960-1999	<120m2	Fair
Weatherboard	1960-1999	<120m2	Poor
Weatherboard	1960-1999	120-200m2	As New
Weatherboard	1960-1999	120-200m2	Good
Weatherboard	1960-1999	120-200m2	Fair
Weatherboard	1960-1999	120-200m2	Poor
Weatherboard	1960-1999	200-300m2	As New
Weatherboard	1960-1999	200-300m2	Good
Weatherboard	1960-1999	200-300m2	Fair
Weatherboard	1960-1999	200-300m2	Poor
Weatherboard	1960-1999	>300m2	As New
Weatherboard	1960-1999	>300m2	Good
Weatherboard	1960-1999	>300m2	Fair
Weatherboard	1960-1999	>300m2	Poor
Weatherboard	2000-current	<120m2	As New
Weatherboard	2000-current	<120m2	Good
Weatherboard	2000-current	<120m2	Fair
Weatherboard	2000-current	<120m2	Poor
Weatherboard	2000-current	120-200m2	As New
Weatherboard	2000-current	120-200m2	Good
Weatherboard	2000-current	120-200m2	Fair
Weatherboard	2000-current	120-200m2	Poor
Weatherboard	2000-current	200-300m2	As New
Weatherboard	2000-current	200-300m2	Good
Weatherboard	2000-current	200-300m2	Fair
Weatherboard	2000-current	200-300m2	Poor
Weatherboard	2000-current	>300m2	As New
Weatherboard	2000-current	>300m2	Good
Weatherboard	2000-current	>300m2	Fair
Weatherboard	2000-current	>300m2	Poor
Fibrous Cement	Pre 1930	<120m2	As New
Fibrous Cement	Pre 1930	<120m2	Good
Fibrous Cement	Pre 1930	<120m2	Fair
Fibrous Cement	Pre 1930	<120m2	Poor
Fibrous Cement	Pre 1930	120-200m2	As New
Fibrous Cement	Pre 1930	120-200m2	Good
Fibrous Cement	Pre 1930	120-200m2	Fair
Fibrous Cement	Pre 1930	120-200m2	Poor
Fibrous Cement	Pre 1930	200-300m2	As New
Fibrous Cement	Pre 1930	200-300m2	Good
Fibrous Cement	Pre 1930	200-300m2	Fair
Fibrous Cement	Pre 1930	200-300m2	Poor
Fibrous Cement	Pre 1930	>300m2	As New
Fibrous Cement	Pre 1930	>300m2	Good
Fibrous Cement	Pre 1930	>300m2	Fair
Fibrous Cement	Pre 1930	>300m2	Poor
Fibrous Cement	1930-1945	<120m2	As New
Fibrous Cement	1930-1945	<120m2	Good
Fibrous Cement	1930-1945	<120m2	Fair
Fibrous Cement	1930-1945	<120m2	Poor
Fibrous Cement	1930-1945	120-200m2	As New
Fibrous Cement	1930-1945	120-200m2	Good
Fibrous Cement	1930-1945	120-200m2	Fair
Fibrous Cement	1930-1945	120-200m2	Poor
Fibrous Cement	1930-1945	200-300m2	As New
Fibrous Cement	1930-1945	200-300m2	Good
Fibrous Cement	1930-1945	200-300m2	Fair
Fibrous Cement	1930-1945	200-300m2	Poor
Fibrous Cement	1930-1945	>300m2	As New
Fibrous Cement	1930-1945	>300m2	Good
Fibrous Cement	1930-1945	>300m2	Fair
Fibrous Cement	1930-1945	>300m2	Poor
Fibrous Cement	1945-1960	<120m2	As New
Fibrous Cement	1945-1960	<120m2	Good
Fibrous Cement	1945-1960	<120m2	Fair
Fibrous Cement	1945-1960	<120m2	Poor
Fibrous Cement	1945-1960	120-200m2	As New
Fibrous Cement	1945-1960	120-200m2	Good
Fibrous Cement	1945-1960	120-200m2	Fair
Fibrous Cement	1945-1960	120-200m2	Poor
Fibrous Cement	1945-1960	200-300m2	As New
Fibrous Cement	1945-1960	200-300m2	Good
Fibrous Cement	1945-1960	200-300m2	Fair
Fibrous Cement	1945-1960	200-300m2	Poor
Fibrous Cement	1945-1960	>300m2	As New
Fibrous Cement	1945-1960	>300m2	Good
Fibrous Cement	1945-1960	>300m2	Fair
Fibrous Cement	1945-1960	>300m2	Poor
Fibrous Cement	1960-1999	<120m2	As New
Fibrous Cement	1960-1999	<120m2	Good
Fibrous Cement	1960-1999	<120m2	Fair
Fibrous Cement	1960-1999	<120m2	Poor
Fibrous Cement	1960-1999	120-200m2	As New
Fibrous Cement	1960-1999	120-200m2	Good
Fibrous Cement	1960-1999	120-200m2	Fair
Fibrous Cement	1960-1999	120-200m2	Poor
Fibrous Cement	1960-1999	200-300m2	As New
Fibrous Cement	1960-1999	200-300m2	Good
Fibrous Cement	1960-1999	200-300m2	Fair
Fibrous Cement	1960-1999	200-300m2	Poor
Fibrous Cement	1960-1999	>300m2	As New
Fibrous Cement	1960-1999	>300m2	Good
Fibrous Cement	1960-1999	>300m2	Fair
Fibrous Cement	1960-1999	>300m2	Poor
Fibrous Cement	2000-current	<120m2	As New
Fibrous Cement	2000-current	<120m2	Good
Fibrous Cement	2000-current	<120m2	Fair
Fibrous Cement	2000-current	<120m2	Poor
Fibrous Cement	2000-current	120-200m2	As New
Fibrous Cement	2000-current	120-200m2	Good
Fibrous Cement	2000-current	120-200m2	Fair
Fibrous Cement	2000-current	120-200m2	Poor
Fibrous Cement	2000-current	200-300m2	As New
Fibrous Cement	2000-current	200-300m2	Good
Fibrous Cement	2000-current	200-300m2	Fair
Fibrous Cement	2000-current	200-300m2	Poor
Fibrous Cement	2000-current	>300m2	As New
Fibrous Cement	2000-current	>300m2	Good
Fibrous Cement	2000-current	>300m2	Fair
Fibrous Cement	2000-current	>300m2	Poor

<tbody>
</tbody>

 

[brin]

Hi
Thanks for the table.
I just realized that the table I posted didn't show the type of construction as a separate column.

Not sure what to do with the table, SQL etc?

<tbody>
</tbody>

 

[Legacy 143009]

Nothing to do with SQL. It was just an example. Now, write down the prices one by one to the fifth column. This will be your support Sheet2. Then, on your main Sheet1 assume A1 = Weatherboard, B1 = 1945 - 1960, C1 = <120m2 and D1 = Poor. All you have to do is write a lookup formula in E1. Something like E1 = VLOOKUP(A1&B1&C1&D1, Sheet2!$A$1:$E$320, 5, 0)

I am not in front of the computer. You may have to mess around with lookup formula a bit

 

[brin]

Hi
I tried the formula but couldn't get it to work and don't know what to change to fix it.

Sheet1

	A	B	C	D	E
1	Weatherboard	1945-1960	<120m2	Fair	#NAME?

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:104px;"><col style="width:76px;"><col style="width:64px;"><col style="width:64px;"><col style="width:84px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 =VLOOKUP(AI&B1&C1&D1,Sheet2!$A$1:$E$320,5,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Sheet2

	A	B	C	D	E
1	Double Brick	Pre 1930	<120m2	As New	900
2	Double Brick	Pre 1930	<120m2	Good	889
3	Double Brick	Pre 1930	<120m2	Fair	888
4	Double Brick	Pre 1930	<120m2	Poor	880
5	Double Brick	Pre 1930	120-200m2	As New	874
6	Double Brick	Pre 1930	120-200m2	Good	868
7	Double Brick	Pre 1930	120-200m2	Fair	862

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

Excel tables to the web >> Excel Jeanie HTML 4

 

[Legacy 143009]

Hi
I tried the formula but couldn't get it to work and don't know what to change to fix it.

Sheet1

	A	B	C	D	E
1	Weatherboard	1945-1960	<120m2	Fair	#NAME?

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 =VLOOKUP(AI&B1&C1&D1,Sheet2!$A$1:$E$320,5,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Sheet2

	A	B	C	D	E
1	Double Brick	Pre 1930	<120m2	As New	900
2	Double Brick	Pre 1930	<120m2	Good	889
3	Double Brick	Pre 1930	<120m2	Fair	888
4	Double Brick	Pre 1930	<120m2	Poor	880
5	Double Brick	Pre 1930	120-200m2	As New	874
6	Double Brick	Pre 1930	120-200m2	Good	868
7	Double Brick	Pre 1930	120-200m2	Fair	862

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Ok then try this one. It will work:

{=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))}

This is an array formula so you have to Ctrl+Shift+Enter after paste

 

[brin]

Ok then try this one. It will work:

{=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))}

This is an array formula so you have to Ctrl+Shift+Enter after paste

I have copied the formula and clicked Ctrl+Shift+Enter and it show the curvy array quotes but only the formula shows and not the result:

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

1

Weatherboard

1945-1960

<120m2

Fair

{=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>

Maybe I am doing something wrong.
I also retyped the formula and it said;too many arguments for this funtion.
I think I copied it exactly.

Excel tables to the web >> Excel Jeanie HTML 4

 

[Legacy 143009]

It is working for me:

Sample.xlsx

 

[brin]

Many thanks.
I copied the sample file and it works.
Brilliant !
Thanks for all your good work and persistence.

 

[Legacy 143009]

Your welcome