Relationship between tables doesn't seem to work for some values

[Waimea]

Hi, I have two tables, one data table and one lookup table.

However, the lookup table doesn't seem to work for all values of the lookup table below:

[TABLE]
<tbody>[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD][B]1-5[/B][/TD]
[/TR]
[TR]
[TD][B]6-15[/B][/TD]
[/TR]
[TR]
[TD]16-19[/TD]
[/TR]
[TR]
[TD]20-29[/TD]
[/TR]
[TR]
[TD]30-39[/TD]
[/TR]
[TR]
[TD]40-49[/TD]
[/TR]
[TR]
[TD]50-64[/TD]
[/TR]
[TR]
[TD]65-74[/TD]
[/TR]
[TR]
[TD]75-79[/TD]
[/TR]
[TR]
[TD]80-89[/TD]
[/TR]
[TR]
[TD]90-w[/TD]
[/TR]
</tbody>[/TABLE]

For lookup table value of 1-5 and 6-15 the relationship doesn't seem to work and the values gives me a total instead of a part of a sum. I am thinking that this could be because of sorting or something similar to that.

Is there any workaround for this?

In the image below, the 6-15 should give me 5235 and it gives me 44955 and I don't know why??

https://ibb.co/r6QQG1y

 

[Waimea]

Any suggestions on how to fix this problem? It really bothers me that I can't figure out why the lookup doesn't work!

 

[Waimea]

I have crossposted this at http://www.vbaexpress.com/forum/sho...e-gives-the-wrong-results&p=387056#post387056

 

[Aladin Akyurek]

Would you post the sample using one of the methods mentioned in:

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Waimea]

	A	B	C
City	Age
City (Suburb 1)
City (Suburb 1)	1-5
City (Suburb 1)	6-15
City (Suburb 1)	16-19
City (Suburb 1)	20-29
City (Suburb 1)	30-39
City (Suburb 1)	40-49
City (Suburb 1)	50-64
City (Suburb 1)	65-74
City (Suburb 1)	75-79
City (Suburb 1)	80-89
City (Suburb 1)	90-w

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2017[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1135[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]4395[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]5455[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]1835[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]14265[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]15155[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]8720[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]10515[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]5105[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]1475[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]1645[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]410[/TD]

</tbody>

Column A is city name, column B is the same as my lookup table, column C is the population in that age group.

The lookup table works for age 0, 16-19, 20-29, 30-39, 40-49, 50-64, 65-74, 75-79, 80-80 and 90-w.

It doesn't work for age 1-5 and 6-15.

 

[Waimea]

Could age 1-5 and 6-15 be interpreted as dates?

 

[Aladin Akyurek]

Could age 1-5 and 6-15 be interpreted as dates?

Change the set up like below:

Book1
	A	B	C	D
1	City	Age		2017
14	City (Suburb 1)	0		1135
15	City (Suburb 1)	1	5	4395
16	City (Suburb 1)	6	15	5455
17	City (Suburb 1)	16	19	1835
18	City (Suburb 1)	20	29	14265
19	City (Suburb 1)	30	39	15155
20	City (Suburb 1)	40	49	8720
21	City (Suburb 1)	50	64	10515
22	City (Suburb 1)	65	74	5105
23	City (Suburb 1)	75	79	1475
24	City (Suburb 1)	80	89	1645
25	City (Suburb 1)	90	w	410
Sheet1

 

[Waimea]

Hi Aladin,

I have changed my setup as per your very clever suggestion!

I am using PowerPivot but I am unsure how to set up my lookup table?

2017 1-5:=CALCULATE([Population 2017] ;'Age'[Age] >="1" && 'Age'[Age] <="5")

 

[Waimea]

I got it to work. Thank you very much Aladin!

Your suggestion is really good and clever!

 

[Waimea]

If someone has the time, please show me how to set up my lookup tablesfor this set up??