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:

[TABLE="width: 554"]
<tbody>[TR]
[TD]Double brick[/TD]
[TD]1960-1999[/TD]
[TD]120-200m2[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]
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
Double Brick2000-current>300m2Poor
Brick Veneer1960-1999200-300m2Fair
Weatherboard1945-1960120-200m2Good
Fibrous Cement1930-1945<120m2As New
Pre 1930

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Construction:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Age:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Size:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]Condition:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffcc]#ffffcc[/URL] , align: center"]Range:[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: #ccffcc"]Double Brick[/TD]
[TD="bgcolor: #ccffcc"]1960-1999[/TD]
[TD="bgcolor: #ccffcc"]120-200m2[/TD]
[TD="bgcolor: #ccffcc"]Good[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]$1000-$1299[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4





Lookup

Lookup

GHIJKLMN
2000-current200-300m2 $350-$649$650-$949$950-$1299$1300-$1750
1960-1999$300-$599$600-$899$900-$1249$1250-$1700
1945-1960$250-$549$550-$849$850-$1199$1200-$1650
1930-1945$200-$499$500-$799$800-$1149$1150-$1600
Pre 1930$150-$449$450-$749$750-$1099$1100-$1550
2000-current<120m2 $550-$849$850-$1149$1150-$1449$1500-$1950
1960-1999$500-$799$800-$1099$1100-$1399$1400-$1900
1945-1960$450-$749$750-$1049$1050-$1349$1350-$1850
1930-1945$400-$699$700-$999$1000-$1299$1300-$1800
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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]Construction[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]Age[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] , align: center"]Condition[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]Size[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]Poor[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]Fair[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]Good[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=003366]#003366[/URL] "]As new[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="bgcolor: #ccccff"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]2000-current[/TD]
[TD="bgcolor: #ccccff"]>300m2 [/TD]
[TD="bgcolor: #ccccff"]$300-$599[/TD]
[TD="bgcolor: #ccccff"]$600-$899[/TD]
[TD="bgcolor: #ccccff"]$900-$1249[/TD]
[TD="bgcolor: #ccccff"]$1250-$1700[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: #ccccff"]1960-1999[/TD]
[TD="bgcolor: #ccccff"]$250-$549[/TD]
[TD="bgcolor: #ccccff"]$550-$849[/TD]
[TD="bgcolor: #ccccff"]$850-$1149[/TD]
[TD="bgcolor: #ccccff"]$1150-$1650[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: #ccccff"]1945-1960[/TD]
[TD="bgcolor: #ccccff"]$200-$499[/TD]
[TD="bgcolor: #ccccff"]$500-$799[/TD]
[TD="bgcolor: #ccccff"]$800-$1100[/TD]
[TD="bgcolor: #ccccff"]$1100-$1600[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="bgcolor: #ccccff"]1930-1945[/TD]
[TD="bgcolor: #ccccff"]$150-$449[/TD]
[TD="bgcolor: #ccccff"]$450-$749[/TD]
[TD="bgcolor: #ccccff"]$750-$1049[/TD]
[TD="bgcolor: #ccccff"]$1050-$1550[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="bgcolor: #ccccff"]Pre 1930[/TD]
[TD="bgcolor: #ccccff"]$100-$399[/TD]
[TD="bgcolor: #ccccff"]$449-$699[/TD]
[TD="bgcolor: #ccccff"]$700-$999[/TD]
[TD="bgcolor: #ccccff"]$1000-$1500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="bgcolor: #ccccff"]2000-current[/TD]
[TD="bgcolor: #ccccff"]120-200m2 [/TD]
[TD="bgcolor: #ccccff"]$450-$749[/TD]
[TD="bgcolor: #ccccff"]$750-$1049[/TD]
[TD="bgcolor: #ccccff"]$1050-$1349[/TD]
[TD="bgcolor: #ccccff"]$1350-$1850[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="bgcolor: #ccccff"]1960-1999[/TD]
[TD="bgcolor: #ccccff"]$400-$699[/TD]
[TD="bgcolor: #ccccff"]$700-$999[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]$1000-$1299[/TD]
[TD="bgcolor: #ccccff"]$1300-$1800[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="bgcolor: #ccccff"]1945-1960[/TD]
[TD="bgcolor: #ccccff"]$350-$649[/TD]
[TD="bgcolor: #ccccff"]$650-$949[/TD]
[TD="bgcolor: #ccccff"]$950-$1249[/TD]
[TD="bgcolor: #ccccff"]$1250-$1750[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="bgcolor: #ccccff"]1930-1945[/TD]
[TD="bgcolor: #ccccff"]$300-$599[/TD]
[TD="bgcolor: #ccccff"]$600-$899[/TD]
[TD="bgcolor: #ccccff"]$900-$1199[/TD]
[TD="bgcolor: #ccccff"]$1200-$1700[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="bgcolor: #ccccff"]Pre 1930[/TD]
[TD="bgcolor: #ccccff"]$250-$549[/TD]
[TD="bgcolor: #ccccff"]$550-$849[/TD]
[TD="bgcolor: #ccccff"]$850-$1149[/TD]
[TD="bgcolor: #ccccff"]$1150-$1650[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]

</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
 
;)
I have carefully revised the data on the spreadsheet and now it works perfectly.
Don't know what was wrong with the way I copied it before or what has made it work, but it is working properly now, so that is great.
Many thanks for all your help.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
In Sample2.xlsx, on the data validation list, "200-300m2 " has a trailing space for example. You must be careful.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

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
Back
Top