HLOOKUP in Access 2013

[AndyDevine1]

Hi there

To simplify, I have three fields in my table:

Column A "Item" contains
A2 - Item 1
A3 - Item 2


Column B contains the heading "Item 1" and then values in B2 (eg 100) and B3 (eg 50)
Column C contains the heading "Item 2" and then values in C2 (eg 11) and C3 (eg 12)

I want to report on the totals for each column (B, C etc)

So I want Item 1 total = 100 + 150 = 150
And item 2 total = 11 + 12 = 25

Can I do this via a horizontal look up or similar?

Thanks for reading

Andy

 

[sheetspread]

You can use the total row on the table or create a report with totals. An HLOOKUP equivalent might give you the nth record in a field, but not an aggregate value. Where do you want them to appear?

 

[AndyDevine1]

You can use the total row on the table or create a report with totals. An HLOOKUP equivalent might give you the nth record in a field, but not an aggregate value. Where do you want them to appear?

Hi thanks for your reply. A new acess table. How do I get the following

'Cell A2' to be 'item 1'
'Cell B2' to be '150'
'Cell A3' to be 'item 2'
'Cell B3' to be '15'

Thanks

 

[AndyDevine1]

Sorry should read 25not 15

 

[sheetspread]

https://support.office.com/en-sg/ar...e54-8994-f6a0417b80d3?ui=en-US&rs=en-SG&ad=SG

The totals row is available in your version but none earlier than 2007. I have 2003 so I can't test it, but let me know your results.

 

[AndyDevine1]

https://support.office.com/en-sg/ar...e54-8994-f6a0417b80d3?ui=en-US&rs=en-SG&ad=SG

The totals row is available in your version but none earlier than 2007. I have 2003 so I can't test it, but let me know your results.

Thanks again for your reply

In that example in the first column and rows there is "item 1", "item 2" etc....

In my case, I have the same - "item 1", "item 2" , "Item 3" , item, item 5 etc, etc....

In column B I have numbers for "item 1" and in column C I have item 2, D item 3, E item 4 etc, etc

So I want to know to be able to say in Column A what the item number is...eg item 1 and what the total for item 1 is in column B

Item 2 in column A and total for item 2 in column B

Item 3 in column A and total for item 3 in column B

etc etc

Can you help with that?

Many thanks

 

[sheetspread]

Can you type what you've described above in a table-like format? I'm not sure if you want grand totals, subtotals, cross totals, or something else.

 

[AndyDevine1]

In A1 I have my heading "Item"
In A2 - "Item 1"
In A3 - "Item 3"
In A4 - "Item 4"

And so on....for 20 items

In B1 I have heading "Item 1", C1 "Item 2", D1 "Item 3" and so on....imagine 20 columns each representing an Item - item 1 through to item 20...

In Cells C2 right the way down to end of the list and to the right I have numbers


I want item number in column A and the total for the corresponding item in column B

Can you help?

 

[sheetspread]

Like this?:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Item	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10	Item 11	Item 12	Item 13	Item 14	Item 15	Item 16	Item 17	Item 18	Item 19	Item 20
2	Item 1	42	47	74	13	84	19	79	82	31	79	97	3	55	3	52	43	54	97	33	6
3	Item 2	26	11	41	78	99	8	42	73	42	45	52	69	53	35	68	15	26	82	46	52
4	Item 3	52	54	10	77	52	11	94	11	23	5	62	86	63	3	9	19	93	87	2	26
5	Item 4	33	26	42	84	61	79	17	54	60	24	43	25	20	87	32	42	4	38	6	75
6	Item 5	48	68	96	73	63	74	34	22	58	85	84	9	6	61	30	23	17	81	28	20
7	Item 6	66	89	27	49	57	35	62	88	41	43	26	46	91	66	90	27	6	19	9	62
8	Item 7	97	68	46	16	99	53	100	46	17	2	79	86	92	86	42	66	64	59	81	27
9	Item 8	81	66	79	39	37	52	51	99	66	49	49	36	56	24	37	97	48	1	76	84
10	Item 9	2	66	39	65	91	57	7	94	91	21	19	4	39	17	89	42	37	12	91	50
11	Item 10	53	24	40	90	72	7	1	76	8	47	24	20	70	72	90	75	5	23	45	10
12	Item 11	41	71	59	65	27	83	11	31	63	52	30	54	34	46	82	39	95	66	81	15
13	Item 12	22	60	53	4	27	99	4	53	86	16	35	70	49	29	17	26	57	43	93	4
14	Item 13	100	8	38	52	62	24	45	21	30	49	40	3	16	21	31	78	40	86	44	51
15	Item 14	60	10	79	16	53	76	95	37	52	76	92	28	62	11	66	6	98	62	7	87
16	Item 15	82	4	32	54	78	69	66	53	80	15	100	60	26	99	72	2	41	82	71	99
17	Item 16	21	23	70	47	65	95	16	16	63	46	41	35	91	64	35	5	24	8	96	19
18	Item 17	5	6	37	30	28	67	50	55	69	35	59	79	16	1	38	9	58	10	8	30
19	Item 18	23	87	34	75	86	54	4	29	35	30	70	45	91	34	10	66	61	94	19	64
20	Item 19	53	52	70	28	1	13	43	29	83	15	63	53	35	52	15	60	54	15	37	35
21	Item 20	97	54	76	2	25	45	51	43	94	90	96	11	99	64	11	10	4	96	37	76
22
23	Item 1	1004
24	Item 2	894
25	Item 3	1042
26	Item 4	957
27	Item 5	1167
28	Item 6	1020
29	Item 7	872
30	Item 8	1012
31	Item 9	1092
32	Item 10	824
33	Item 11	1161
34	Item 12	822
35	Item 13	1064
36	Item 14	875
37	Item 15	916
38	Item 16	750
39	Item 17	886
40	Item 18	1061
41	Item 19	910
42	Item 20	892
Sheet13

 

[AndyDevine1]

Like this?:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Item	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10	Item 11	Item 12	Item 13	Item 14	Item 15	Item 16	Item 17	Item 18	Item 19	Item 20
2	Item 1	42	47	74	13	84	19	79	82	31	79	97	3	55	3	52	43	54	97	33	6
3	Item 2	26	11	41	78	99	8	42	73	42	45	52	69	53	35	68	15	26	82	46	52
4	Item 3	52	54	10	77	52	11	94	11	23	5	62	86	63	3	9	19	93	87	2	26
5	Item 4	33	26	42	84	61	79	17	54	60	24	43	25	20	87	32	42	4	38	6	75
6	Item 5	48	68	96	73	63	74	34	22	58	85	84	9	6	61	30	23	17	81	28	20
7	Item 6	66	89	27	49	57	35	62	88	41	43	26	46	91	66	90	27	6	19	9	62
8	Item 7	97	68	46	16	99	53	100	46	17	2	79	86	92	86	42	66	64	59	81	27
9	Item 8	81	66	79	39	37	52	51	99	66	49	49	36	56	24	37	97	48	1	76	84
10	Item 9	2	66	39	65	91	57	7	94	91	21	19	4	39	17	89	42	37	12	91	50
11	Item 10	53	24	40	90	72	7	1	76	8	47	24	20	70	72	90	75	5	23	45	10
12	Item 11	41	71	59	65	27	83	11	31	63	52	30	54	34	46	82	39	95	66	81	15
13	Item 12	22	60	53	4	27	99	4	53	86	16	35	70	49	29	17	26	57	43	93	4
14	Item 13	100	8	38	52	62	24	45	21	30	49	40	3	16	21	31	78	40	86	44	51
15	Item 14	60	10	79	16	53	76	95	37	52	76	92	28	62	11	66	6	98	62	7	87
16	Item 15	82	4	32	54	78	69	66	53	80	15	100	60	26	99	72	2	41	82	71	99
17	Item 16	21	23	70	47	65	95	16	16	63	46	41	35	91	64	35	5	24	8	96	19
18	Item 17	5	6	37	30	28	67	50	55	69	35	59	79	16	1	38	9	58	10	8	30
19	Item 18	23	87	34	75	86	54	4	29	35	30	70	45	91	34	10	66	61	94	19	64
20	Item 19	53	52	70	28	1	13	43	29	83	15	63	53	35	52	15	60	54	15	37	35
21	Item 20	97	54	76	2	25	45	51	43	94	90	96	11	99	64	11	10	4	96	37	76
22
23	Item 1	1004
24	Item 2	894
25	Item 3	1042
26	Item 4	957
27	Item 5	1167
28	Item 6	1020
29	Item 7	872
30	Item 8	1012
31	Item 9	1092
32	Item 10	824
33	Item 11	1161
34	Item 12	822
35	Item 13	1064
36	Item 14	875
37	Item 15	916
38	Item 16	750
39	Item 17	886
40	Item 18	1061
41	Item 19	910
42	Item 20	892
Sheet13

Yes thanks , do you know how to do that? Cheers