Lookup table/Index

brin

Board Regular
Joined
Sep 7, 2008
Messages
202
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 brick1960-1999120-200m2Good

<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

ABCDE
1Construction:Age:Size:Condition:Range:
2Double Brick1960-1999120-200m2Good$1000-$1299
3Double Brick2000-current>300m2Poor
4Brick Veneer1960-1999200-300m2Fair
5Weatherboard1945-1960120-200m2Good
6Fibrous Cement1930-1945<120m2As New
7Pre 1930

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4





Lookup

Lookup

GHIJKLMN
3ConstructionAge Condition
4SizePoorFairGoodAs new
5Double Brick2000-current>300m2 $300-$599$600-$899$900-$1249$1250-$1700
61960-1999$250-$549$550-$849$850-$1149$1150-$1650
71945-1960$200-$499$500-$799$800-$1100$1100-$1600
81930-1945$150-$449$450-$749$750-$1049$1050-$1550
9Pre 1930$100-$399$449-$699$700-$999$1000-$1500
102000-current200-300m2 $350-$649$650-$949$950-$1299$1300-$1750
111960-1999$300-$599$600-$899$900-$1249$1250-$1700
121945-1960$250-$549$550-$849$850-$1199$1200-$1650
131930-1945$200-$499$500-$799$800-$1149$1150-$1600
14Pre 1930$150-$449$450-$749$750-$1099$1100-$1550
152000-current120-200m2 $450-$749$750-$1049$1050-$1349$1350-$1850
161960-1999$400-$699$700-$999$1000-$1299$1300-$1800
171945-1960$350-$649$650-$949$950-$1249$1250-$1750
181930-1945$300-$599$600-$899$900-$1199$1200-$1700
19Pre 1930$250-$549$550-$849$850-$1149$1150-$1650
202000-current<120m2 $550-$849$850-$1149$1150-$1449$1500-$1950
211960-1999$500-$799$800-$1099$1100-$1399$1400-$1900
221945-1960$450-$749$750-$1049$1050-$1349$1350-$1850
231930-1945$400-$699$700-$999$1000-$1299$1300-$1800
24Pre 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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
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 BrickPre 1930<120m2As New
Double BrickPre 1930<120m2Good
Double BrickPre 1930<120m2Fair
Double BrickPre 1930<120m2Poor
Double BrickPre 1930120-200m2As New
Double BrickPre 1930120-200m2Good
Double BrickPre 1930120-200m2Fair
Double BrickPre 1930120-200m2Poor
Double BrickPre 1930200-300m2As New
Double BrickPre 1930200-300m2Good
Double BrickPre 1930200-300m2Fair
Double BrickPre 1930200-300m2Poor
Double BrickPre 1930>300m2As New
Double BrickPre 1930>300m2Good
Double BrickPre 1930>300m2Fair
Double BrickPre 1930>300m2Poor
Double Brick1930-1945<120m2As New
Double Brick1930-1945<120m2Good
Double Brick1930-1945<120m2Fair
Double Brick1930-1945<120m2Poor
Double Brick1930-1945120-200m2As New
Double Brick1930-1945120-200m2Good
Double Brick1930-1945120-200m2Fair
Double Brick1930-1945120-200m2Poor
Double Brick1930-1945200-300m2As New
Double Brick1930-1945200-300m2Good
Double Brick1930-1945200-300m2Fair
Double Brick1930-1945200-300m2Poor
Double Brick1930-1945>300m2As New
Double Brick1930-1945>300m2Good
Double Brick1930-1945>300m2Fair
Double Brick1930-1945>300m2Poor
Double Brick1945-1960<120m2As New
Double Brick1945-1960<120m2Good
Double Brick1945-1960<120m2Fair
Double Brick1945-1960<120m2Poor
Double Brick1945-1960120-200m2As New
Double Brick1945-1960120-200m2Good
Double Brick1945-1960120-200m2Fair
Double Brick1945-1960120-200m2Poor
Double Brick1945-1960200-300m2As New
Double Brick1945-1960200-300m2Good
Double Brick1945-1960200-300m2Fair
Double Brick1945-1960200-300m2Poor
Double Brick1945-1960>300m2As New
Double Brick1945-1960>300m2Good
Double Brick1945-1960>300m2Fair
Double Brick1945-1960>300m2Poor
Double Brick1960-1999<120m2As New
Double Brick1960-1999<120m2Good
Double Brick1960-1999<120m2Fair
Double Brick1960-1999<120m2Poor
Double Brick1960-1999120-200m2As New
Double Brick1960-1999120-200m2Good
Double Brick1960-1999120-200m2Fair
Double Brick1960-1999120-200m2Poor
Double Brick1960-1999200-300m2As New
Double Brick1960-1999200-300m2Good
Double Brick1960-1999200-300m2Fair
Double Brick1960-1999200-300m2Poor
Double Brick1960-1999>300m2As New
Double Brick1960-1999>300m2Good
Double Brick1960-1999>300m2Fair
Double Brick1960-1999>300m2Poor
Double Brick2000-current<120m2As New
Double Brick2000-current<120m2Good
Double Brick2000-current<120m2Fair
Double Brick2000-current<120m2Poor
Double Brick2000-current120-200m2As New
Double Brick2000-current120-200m2Good
Double Brick2000-current120-200m2Fair
Double Brick2000-current120-200m2Poor
Double Brick2000-current200-300m2As New
Double Brick2000-current200-300m2Good
Double Brick2000-current200-300m2Fair
Double Brick2000-current200-300m2Poor
Double Brick2000-current>300m2As New
Double Brick2000-current>300m2Good
Double Brick2000-current>300m2Fair
Double Brick2000-current>300m2Poor
Brick VeneerPre 1930<120m2As New
Brick VeneerPre 1930<120m2Good
Brick VeneerPre 1930<120m2Fair
Brick VeneerPre 1930<120m2Poor
Brick VeneerPre 1930120-200m2As New
Brick VeneerPre 1930120-200m2Good
Brick VeneerPre 1930120-200m2Fair
Brick VeneerPre 1930120-200m2Poor
Brick VeneerPre 1930200-300m2As New
Brick VeneerPre 1930200-300m2Good
Brick VeneerPre 1930200-300m2Fair
Brick VeneerPre 1930200-300m2Poor
Brick VeneerPre 1930>300m2As New
Brick VeneerPre 1930>300m2Good
Brick VeneerPre 1930>300m2Fair
Brick VeneerPre 1930>300m2Poor
Brick Veneer1930-1945<120m2As New
Brick Veneer1930-1945<120m2Good
Brick Veneer1930-1945<120m2Fair
Brick Veneer1930-1945<120m2Poor
Brick Veneer1930-1945120-200m2As New
Brick Veneer1930-1945120-200m2Good
Brick Veneer1930-1945120-200m2Fair
Brick Veneer1930-1945120-200m2Poor
Brick Veneer1930-1945200-300m2As New
Brick Veneer1930-1945200-300m2Good
Brick Veneer1930-1945200-300m2Fair
Brick Veneer1930-1945200-300m2Poor
Brick Veneer1930-1945>300m2As New
Brick Veneer1930-1945>300m2Good
Brick Veneer1930-1945>300m2Fair
Brick Veneer1930-1945>300m2Poor
Brick Veneer1945-1960<120m2As New
Brick Veneer1945-1960<120m2Good
Brick Veneer1945-1960<120m2Fair
Brick Veneer1945-1960<120m2Poor
Brick Veneer1945-1960120-200m2As New
Brick Veneer1945-1960120-200m2Good
Brick Veneer1945-1960120-200m2Fair
Brick Veneer1945-1960120-200m2Poor
Brick Veneer1945-1960200-300m2As New
Brick Veneer1945-1960200-300m2Good
Brick Veneer1945-1960200-300m2Fair
Brick Veneer1945-1960200-300m2Poor
Brick Veneer1945-1960>300m2As New
Brick Veneer1945-1960>300m2Good
Brick Veneer1945-1960>300m2Fair
Brick Veneer1945-1960>300m2Poor
Brick Veneer1960-1999<120m2As New
Brick Veneer1960-1999<120m2Good
Brick Veneer1960-1999<120m2Fair
Brick Veneer1960-1999<120m2Poor
Brick Veneer1960-1999120-200m2As New
Brick Veneer1960-1999120-200m2Good
Brick Veneer1960-1999120-200m2Fair
Brick Veneer1960-1999120-200m2Poor
Brick Veneer1960-1999200-300m2As New
Brick Veneer1960-1999200-300m2Good
Brick Veneer1960-1999200-300m2Fair
Brick Veneer1960-1999200-300m2Poor
Brick Veneer1960-1999>300m2As New
Brick Veneer1960-1999>300m2Good
Brick Veneer1960-1999>300m2Fair
Brick Veneer1960-1999>300m2Poor
Brick Veneer2000-current<120m2As New
Brick Veneer2000-current<120m2Good
Brick Veneer2000-current<120m2Fair
Brick Veneer2000-current<120m2Poor
Brick Veneer2000-current120-200m2As New
Brick Veneer2000-current120-200m2Good
Brick Veneer2000-current120-200m2Fair
Brick Veneer2000-current120-200m2Poor
Brick Veneer2000-current200-300m2As New
Brick Veneer2000-current200-300m2Good
Brick Veneer2000-current200-300m2Fair
Brick Veneer2000-current200-300m2Poor
Brick Veneer2000-current>300m2As New
Brick Veneer2000-current>300m2Good
Brick Veneer2000-current>300m2Fair
Brick Veneer2000-current>300m2Poor
WeatherboardPre 1930<120m2As New
WeatherboardPre 1930<120m2Good
WeatherboardPre 1930<120m2Fair
WeatherboardPre 1930<120m2Poor
WeatherboardPre 1930120-200m2As New
WeatherboardPre 1930120-200m2Good
WeatherboardPre 1930120-200m2Fair
WeatherboardPre 1930120-200m2Poor
WeatherboardPre 1930200-300m2As New
WeatherboardPre 1930200-300m2Good
WeatherboardPre 1930200-300m2Fair
WeatherboardPre 1930200-300m2Poor
WeatherboardPre 1930>300m2As New
WeatherboardPre 1930>300m2Good
WeatherboardPre 1930>300m2Fair
WeatherboardPre 1930>300m2Poor
Weatherboard1930-1945<120m2As New
Weatherboard1930-1945<120m2Good
Weatherboard1930-1945<120m2Fair
Weatherboard1930-1945<120m2Poor
Weatherboard1930-1945120-200m2As New
Weatherboard1930-1945120-200m2Good
Weatherboard1930-1945120-200m2Fair
Weatherboard1930-1945120-200m2Poor
Weatherboard1930-1945200-300m2As New
Weatherboard1930-1945200-300m2Good
Weatherboard1930-1945200-300m2Fair
Weatherboard1930-1945200-300m2Poor
Weatherboard1930-1945>300m2As New
Weatherboard1930-1945>300m2Good
Weatherboard1930-1945>300m2Fair
Weatherboard1930-1945>300m2Poor
Weatherboard1945-1960<120m2As New
Weatherboard1945-1960<120m2Good
Weatherboard1945-1960<120m2Fair
Weatherboard1945-1960<120m2Poor
Weatherboard1945-1960120-200m2As New
Weatherboard1945-1960120-200m2Good
Weatherboard1945-1960120-200m2Fair
Weatherboard1945-1960120-200m2Poor
Weatherboard1945-1960200-300m2As New
Weatherboard1945-1960200-300m2Good
Weatherboard1945-1960200-300m2Fair
Weatherboard1945-1960200-300m2Poor
Weatherboard1945-1960>300m2As New
Weatherboard1945-1960>300m2Good
Weatherboard1945-1960>300m2Fair
Weatherboard1945-1960>300m2Poor
Weatherboard1960-1999<120m2As New
Weatherboard1960-1999<120m2Good
Weatherboard1960-1999<120m2Fair
Weatherboard1960-1999<120m2Poor
Weatherboard1960-1999120-200m2As New
Weatherboard1960-1999120-200m2Good
Weatherboard1960-1999120-200m2Fair
Weatherboard1960-1999120-200m2Poor
Weatherboard1960-1999200-300m2As New
Weatherboard1960-1999200-300m2Good
Weatherboard1960-1999200-300m2Fair
Weatherboard1960-1999200-300m2Poor
Weatherboard1960-1999>300m2As New
Weatherboard1960-1999>300m2Good
Weatherboard1960-1999>300m2Fair
Weatherboard1960-1999>300m2Poor
Weatherboard2000-current<120m2As New
Weatherboard2000-current<120m2Good
Weatherboard2000-current<120m2Fair
Weatherboard2000-current<120m2Poor
Weatherboard2000-current120-200m2As New
Weatherboard2000-current120-200m2Good
Weatherboard2000-current120-200m2Fair
Weatherboard2000-current120-200m2Poor
Weatherboard2000-current200-300m2As New
Weatherboard2000-current200-300m2Good
Weatherboard2000-current200-300m2Fair
Weatherboard2000-current200-300m2Poor
Weatherboard2000-current>300m2As New
Weatherboard2000-current>300m2Good
Weatherboard2000-current>300m2Fair
Weatherboard2000-current>300m2Poor
Fibrous CementPre 1930<120m2As New
Fibrous CementPre 1930<120m2Good
Fibrous CementPre 1930<120m2Fair
Fibrous CementPre 1930<120m2Poor
Fibrous CementPre 1930120-200m2As New
Fibrous CementPre 1930120-200m2Good
Fibrous CementPre 1930120-200m2Fair
Fibrous CementPre 1930120-200m2Poor
Fibrous CementPre 1930200-300m2As New
Fibrous CementPre 1930200-300m2Good
Fibrous CementPre 1930200-300m2Fair
Fibrous CementPre 1930200-300m2Poor
Fibrous CementPre 1930>300m2As New
Fibrous CementPre 1930>300m2Good
Fibrous CementPre 1930>300m2Fair
Fibrous CementPre 1930>300m2Poor
Fibrous Cement1930-1945<120m2As New
Fibrous Cement1930-1945<120m2Good
Fibrous Cement1930-1945<120m2Fair
Fibrous Cement1930-1945<120m2Poor
Fibrous Cement1930-1945120-200m2As New
Fibrous Cement1930-1945120-200m2Good
Fibrous Cement1930-1945120-200m2Fair
Fibrous Cement1930-1945120-200m2Poor
Fibrous Cement1930-1945200-300m2As New
Fibrous Cement1930-1945200-300m2Good
Fibrous Cement1930-1945200-300m2Fair
Fibrous Cement1930-1945200-300m2Poor
Fibrous Cement1930-1945>300m2As New
Fibrous Cement1930-1945>300m2Good
Fibrous Cement1930-1945>300m2Fair
Fibrous Cement1930-1945>300m2Poor
Fibrous Cement1945-1960<120m2As New
Fibrous Cement1945-1960<120m2Good
Fibrous Cement1945-1960<120m2Fair
Fibrous Cement1945-1960<120m2Poor
Fibrous Cement1945-1960120-200m2As New
Fibrous Cement1945-1960120-200m2Good
Fibrous Cement1945-1960120-200m2Fair
Fibrous Cement1945-1960120-200m2Poor
Fibrous Cement1945-1960200-300m2As New
Fibrous Cement1945-1960200-300m2Good
Fibrous Cement1945-1960200-300m2Fair
Fibrous Cement1945-1960200-300m2Poor
Fibrous Cement1945-1960>300m2As New
Fibrous Cement1945-1960>300m2Good
Fibrous Cement1945-1960>300m2Fair
Fibrous Cement1945-1960>300m2Poor
Fibrous Cement1960-1999<120m2As New
Fibrous Cement1960-1999<120m2Good
Fibrous Cement1960-1999<120m2Fair
Fibrous Cement1960-1999<120m2Poor
Fibrous Cement1960-1999120-200m2As New
Fibrous Cement1960-1999120-200m2Good
Fibrous Cement1960-1999120-200m2Fair
Fibrous Cement1960-1999120-200m2Poor
Fibrous Cement1960-1999200-300m2As New
Fibrous Cement1960-1999200-300m2Good
Fibrous Cement1960-1999200-300m2Fair
Fibrous Cement1960-1999200-300m2Poor
Fibrous Cement1960-1999>300m2As New
Fibrous Cement1960-1999>300m2Good
Fibrous Cement1960-1999>300m2Fair
Fibrous Cement1960-1999>300m2Poor
Fibrous Cement2000-current<120m2As New
Fibrous Cement2000-current<120m2Good
Fibrous Cement2000-current<120m2Fair
Fibrous Cement2000-current<120m2Poor
Fibrous Cement2000-current120-200m2As New
Fibrous Cement2000-current120-200m2Good
Fibrous Cement2000-current120-200m2Fair
Fibrous Cement2000-current120-200m2Poor
Fibrous Cement2000-current200-300m2As New
Fibrous Cement2000-current200-300m2Good
Fibrous Cement2000-current200-300m2Fair
Fibrous Cement2000-current200-300m2Poor
Fibrous Cement2000-current>300m2As New
Fibrous Cement2000-current>300m2Good
Fibrous Cement2000-current>300m2Fair
Fibrous Cement2000-current>300m2Poor

<tbody>
</tbody>
 

brin

Board Regular
Joined
Sep 7, 2008
Messages
202
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>
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
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 ;)
 
Last edited:

brin

Board Regular
Joined
Sep 7, 2008
Messages
202

ADVERTISEMENT

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

Sheet1

ABCDE
1Weatherboard1945-1960<120m2Fair#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
CellFormula
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

ABCDE
1Double BrickPre 1930<120m2As New900
2Double BrickPre 1930<120m2Good889
3Double BrickPre 1930<120m2Fair888
4Double BrickPre 1930<120m2Poor880
5Double BrickPre 1930120-200m2As New874
6Double BrickPre 1930120-200m2Good868
7Double BrickPre 1930120-200m2Fair862

<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
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
Hi
I tried the formula but couldn't get it to work and don't know what to change to fix it.

Sheet1

ABCDE
1Weatherboard1945-1960<120m2Fair#NAME?

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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

ABCDE
1Double BrickPre 1930<120m2As New900
2Double BrickPre 1930<120m2Good889
3Double BrickPre 1930<120m2Fair888
4Double BrickPre 1930<120m2Poor880
5Double BrickPre 1930120-200m2As New874
6Double BrickPre 1930120-200m2Good868
7Double BrickPre 1930120-200m2Fair862

<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

Board Regular
Joined
Sep 7, 2008
Messages
202

ADVERTISEMENT

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

ABCDEFGHIJKL
1Weatherboard1945-1960<120m2Fair{=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
 

brin

Board Regular
Joined
Sep 7, 2008
Messages
202
Many thanks.
I copied the sample file and it works.
Brilliant !
Thanks for all your good work and persistence.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,283
Members
417,134
Latest member
fjleroux187

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