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
 

brin

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

ABCDE
1Double Brick1945-1960200-300m2Poor#N/A

<tbody>
</tbody>

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

ABCDE
1Double Brick>2000>300m2Poor$300-$599
2Double Brick1960-1999>300m2Poor$250-$549
3Double Brick1945-1960>300m2Poor$200-$499
4Double Brick1930-1945>300m2Poor$150-$449
5Double BrickPre 1930>300m2Poor$100-$399
6Double Brick>2000200-300m2Poor$350-$649
7Double Brick1960-1999200-300m2Poor$300-$599
8Double Brick1945-1960200-300m2Poor$250-$549
9Double Brick1930-1945200-300m2Poor$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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Flashbond

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

brin

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

brin

Board Regular
Joined
Sep 7, 2008
Messages
202

ADVERTISEMENT

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.
 

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
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

Board Regular
Joined
Sep 7, 2008
Messages
202

ADVERTISEMENT

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

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

Flashbond

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

brin

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

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,943
Members
416,143
Latest member
JoyceMB

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