Lookup table/Index

B

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
 
Sort by date Sort by votes
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

<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: #969696"]Double Brick[/TD]
[TD="bgcolor: #969696"]1945-1960[/TD]
[TD="bgcolor: #969696"]200-300m2[/TD]
[TD="bgcolor: #969696"]Poor[/TD]
[TD="bgcolor: #ff8080"]#N/A[/TD]

</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
Poor$300-$599
Poor$250-$549
Poor$200-$499
Poor$150-$449
Poor$100-$399
Poor$350-$649
Poor$300-$599
Poor$200-$499

<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: #ccccff"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]>2000[/TD]
[TD="bgcolor: #ccccff"]>300m2[/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: #ccccff"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]1960-1999[/TD]
[TD="bgcolor: #ccccff"]>300m2[/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: #ccccff"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]1945-1960[/TD]
[TD="bgcolor: #ccccff"]>300m2[/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: #ccccff"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]1930-1945[/TD]
[TD="bgcolor: #ccccff"]>300m2[/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"]Pre 1930[/TD]
[TD="bgcolor: #ccccff"]>300m2[/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"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]>2000[/TD]
[TD="bgcolor: #ccccff"]200-300m2[/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"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]1960-1999[/TD]
[TD="bgcolor: #ccccff"]200-300m2[/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: #ffff00"]Double Brick[/TD]
[TD="bgcolor: #ffff00"]1945-1960[/TD]
[TD="bgcolor: #ffff00"]200-300m2[/TD]
[TD="bgcolor: #ffff00"]Poor[/TD]
[TD="bgcolor: #ffff00"]$250-$549[/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"]Double Brick[/TD]
[TD="bgcolor: #ccccff"]1930-1945[/TD]
[TD="bgcolor: #ccccff"]200-300m2[/TD]

</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
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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 by a moderator:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
brin said:
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.
Click to expand...
You should upload to cloud (e.g OneDrive, DropBox) and share the link.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
brin said:
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.
Click to expand...
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 by a moderator:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Multiple IF
2
Replies
13
Views
2K
TAPS_MikeDion
TAPS_MikeDion
How_Do_I
  • Question Question
Index Match
Replies
2
Views
853
How_Do_I
How_Do_I
G
  • Question Question
formula needed
Replies
3
Views
560
Fluff
Fluff
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
S
  • Question Question
Date Calculation
Replies
4
Views
825
snjpverma
S

Forum statistics

Threads
1,223,967
Messages
6,175,664
Members
452,666
Latest member
AllexDee

Share this page

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