Round the number, and look up the match correspond value

[hpbaxxter]

Hello,
I have columns with values (The yellow columns),
and I have a cell of Sum (E5)
I need a formula to round the result of H6 and look up the math in the yellow columns and insert the correspond value .
And thanks in advance.

 

[Dave Patton]

It would have helped if your posted your data with the forum's XL2BB.

Review the following and expand to address your question.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	2	27	15			11	5	6.5	7.7	3.02
3	3	3	29	16
4	6	4	31	17			33	18
5	8	5	33	18
6	9	6	35	19
7	11	7	37	20
8	13	8	39	21
9	15	9	41	22
10	17	10	43	23
11	19	11	45	24
12	21	12	47	25
13	23	13	49	26
14	25	14	51	27
4b

Cell Formulas
Range	Formula
G4	G4	=ROUND(SUM(G2:K2),0)
H4	H4	=CHOOSE(MATCH(G4,{1,27},1),VLOOKUP(G4,A2:B14,2,0),VLOOKUP(G4,C2:D14,2,0))

 

[Dave Patton]

Evaluate the suggestions and use the alternative that you prefer.
It is not necessary to name the ranges but it can make the formula easier to read.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2				Data		11	5	6.5	7.7	3.02
3
4				Results	Sum	Corresponding Value				#N/A
5					33.22	18				18
6					33	18	18			#N/A
7
8	1	2	27	15	53	28
9	3	3	29	16	55	29
10	6	4	31	17	57	30
11	8	5	33	18	59	31
12	9	6	35	19	61	32
13	11	7	37	20	63	33
14	13	8	39	21	65	34
15	15	9	41	22	67	35
16	17	10	43	23	69	36
17	19	11	45	24	71	37
18	21	12	47	25	73	38
19	23	13	49	26	75	39
20	25	14	51	27	77	40
4b

Cell Formulas
Range	Formula
E5	E5	=SUM(F2:J2)
F5	F5	=CHOOSE(MATCH(ROUND(E5,0),{1,27,53},1),VLOOKUP(ROUND(E5,0),A8:B20,2,0),VLOOKUP(ROUND(E5,0),C8:D20,2,0),VLOOKUP(ROUND(E5,0),E8:F20,2,0))
E6	E6	=ROUND(SUM(F2:J2),0)
F6	F6	=CHOOSE(MATCH(E6,{1,27,53},1),VLOOKUP(E6,rL_1,2,0),VLOOKUP(E6,rL_2,2,0),VLOOKUP(E6,rL_3,2,0))
G6	G6	=AGGREGATE(14,6,J4:J6,1)
J4	J4	=VLOOKUP(E6,rL_1,2,0)
J5	J5	=VLOOKUP(E6,rL_2,2,0)
J6	J6	=VLOOKUP(E6,rL_3,2,0)

Named Ranges
Name	Refers To	Cells
rL_1	='4b'!$A$8:$B$20	J4, F5:F6
rL_2	='4b'!$C$8:$D$20	J5, F5:F6
rL_3	='4b'!$E$8:$F$20	J6, F5:F6

 

[hpbaxxter]

It would have helped if your posted your data with the forum's XL2BB.

Review the following and expand to address your question.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	1	2	27	15			11	5	6.5	7.7	3.02
3	3	3	29	16
4	6	4	31	17			33	18
5	8	5	33	18
6	9	6	35	19
7	11	7	37	20
8	13	8	39	21
9	15	9	41	22
10	17	10	43	23
11	19	11	45	24
12	21	12	47	25
13	23	13	49	26
14	25	14	51	27
4b

Cell Formulas
Range	Formula
G4	G4	=ROUND(SUM(G2:K2),0)
H4	H4	=CHOOSE(MATCH(G4,{1,27},1),VLOOKUP(G4,A2:B14,2,0),VLOOKUP(G4,C2:D14,2,0))

Thanks for the reply, but you removed the values of yellow columns.
Well, I posed it here, and I hope it will be easy to help me.

example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Lenght	Value	Lenght		Lenght			Data
2	1	2	27	15	53	28		11	5	6.5	7.7	3.02
3	3	3	29	16	55	29
4	6	4	31	17	57	30
5	8	5	33	18	59	31		Sum	correspond value
6	9	6	35	19	61	32		33.22	18
7	11	7	37	20	63	33
8	13	8	39	21	65	34
9	15	9	41	22	67	35
10	17	10	43	23	69	36
11	19	11	45	24	71	37
12	21	12	47	25	73	38
13	23	13	49	26	75	39
14	25	14	51	27	77	40
Sheet1

Cell Formulas
Range	Formula
H6	H6	=SUM(H2:M2)

 

[Dave Patton]

OK I will repost my suggestion.
I had moved the data lower. Your post said " I have a cell of Sum (E5)"
You did not state where the data was located.

Try the alternative that you prefer.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	1	2	27	15	53	28	Data	11	5	6.5	7.7	3.02
3	3	3	29	16	55	29
4	6	4	31	17	57	30	Results	Sum	Corresponding Value		#N/A
5	8	5	33	18	59	31		33.22	18		18
6	9	6	35	19	61	32		33	18	18	#N/A
7	11	7	37	20	63	33
8	13	8	39	21	65	34
9	15	9	41	22	67	35
10	17	10	43	23	69	36
11	19	11	45	24	71	37
12	21	12	47	25	73	38
13	23	13	49	26	75	39
14	25	14	51	27	77	40
4b

Cell Formulas
Range	Formula
H5	H5	=SUM(H2:L2)
I5	I5	=CHOOSE(MATCH(ROUND(H5,0),{1,27,53},1),VLOOKUP(ROUND(H5,0),A2:B14,2,0),VLOOKUP(ROUND(H5,0),C2:D14,2,0),VLOOKUP(ROUND(H5,0),E2:F14,2,0))
K4	K4	=VLOOKUP(H6,rL_1,2,0)
K5	K5	=VLOOKUP(H6,rL_2,2,0)
H6	H6	=ROUND(SUM(H2:L2),0)
I6	I6	=CHOOSE(MATCH(H6,{1,27,53},1),VLOOKUP(H6,rL_1,2,0),VLOOKUP(H6,rL_2,2,0),VLOOKUP(H6,rL_3,2,0))
J6	J6	=AGGREGATE(14,6,K4:K6,1)
K6	K6	=VLOOKUP(H6,rL_3,2,0)

Named Ranges
Name	Refers To	Cells
rL_1	='4b'!$A$2:$B$14	K4, I5:I6
rL_2	='4b'!$C$2:$D$14	K5, I5:I6
rL_3	='4b'!$E$2:$F$14	K6, I5:I6

 

[hpbaxxter]

OK I will repost my suggestion.
I had moved the data lower. Your post said " I have a cell of Sum (E5)"
You did not state where the data was located.

Try the alternative that you prefer.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	1	2	27	15	53	28	Data	11	5	6.5	7.7	3.02
3	3	3	29	16	55	29
4	6	4	31	17	57	30	Results	Sum	Corresponding Value		#N/A
5	8	5	33	18	59	31		33.22	18		18
6	9	6	35	19	61	32		33	18	18	#N/A
7	11	7	37	20	63	33
8	13	8	39	21	65	34
9	15	9	41	22	67	35
10	17	10	43	23	69	36
11	19	11	45	24	71	37
12	21	12	47	25	73	38
13	23	13	49	26	75	39
14	25	14	51	27	77	40
4b

Cell Formulas
Range	Formula
H5	H5	=SUM(H2:L2)
I5	I5	=CHOOSE(MATCH(ROUND(H5,0),{1,27,53},1),VLOOKUP(ROUND(H5,0),A2:B14,2,0),VLOOKUP(ROUND(H5,0),C2:D14,2,0),VLOOKUP(ROUND(H5,0),E2:F14,2,0))
K4	K4	=VLOOKUP(H6,rL_1,2,0)
K5	K5	=VLOOKUP(H6,rL_2,2,0)
H6	H6	=ROUND(SUM(H2:L2),0)
I6	I6	=CHOOSE(MATCH(H6,{1,27,53},1),VLOOKUP(H6,rL_1,2,0),VLOOKUP(H6,rL_2,2,0),VLOOKUP(H6,rL_3,2,0))
J6	J6	=AGGREGATE(14,6,K4:K6,1)
K6	K6	=VLOOKUP(H6,rL_3,2,0)

Named Ranges
Name	Refers To	Cells
rL_1	='4b'!$A$2:$B$14	K4, I5:I6
rL_2	='4b'!$C$2:$D$14	K5, I5:I6
rL_3	='4b'!$E$2:$F$14	K6, I5:I6

E5 become H5

 

[hpbaxxter]

OK I will repost my suggestion.
I had moved the data lower. Your post said " I have a cell of Sum (E5)"
You did not state where the data was located.

Try the alternative that you prefer.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	1	2	27	15	53	28	Data	11	5	6.5	7.7	3.02
3	3	3	29	16	55	29
4	6	4	31	17	57	30	Results	Sum	Corresponding Value		#N/A
5	8	5	33	18	59	31		33.22	18		18
6	9	6	35	19	61	32		33	18	18	#N/A
7	11	7	37	20	63	33
8	13	8	39	21	65	34
9	15	9	41	22	67	35
10	17	10	43	23	69	36
11	19	11	45	24	71	37
12	21	12	47	25	73	38
13	23	13	49	26	75	39
14	25	14	51	27	77	40
4b

Cell Formulas
Range	Formula
H5	H5	=SUM(H2:L2)
I5	I5	=CHOOSE(MATCH(ROUND(H5,0),{1,27,53},1),VLOOKUP(ROUND(H5,0),A2:B14,2,0),VLOOKUP(ROUND(H5,0),C2:D14,2,0),VLOOKUP(ROUND(H5,0),E2:F14,2,0))
K4	K4	=VLOOKUP(H6,rL_1,2,0)
K5	K5	=VLOOKUP(H6,rL_2,2,0)
H6	H6	=ROUND(SUM(H2:L2),0)
I6	I6	=CHOOSE(MATCH(H6,{1,27,53},1),VLOOKUP(H6,rL_1,2,0),VLOOKUP(H6,rL_2,2,0),VLOOKUP(H6,rL_3,2,0))
J6	J6	=AGGREGATE(14,6,K4:K6,1)
K6	K6	=VLOOKUP(H6,rL_3,2,0)

Named Ranges
Name	Refers To	Cells
rL_1	='4b'!$A$2:$B$14	K4, I5:I6
rL_2	='4b'!$C$2:$D$14	K5, I5:I6
rL_3	='4b'!$E$2:$F$14	K6, I5:I6

Can you keep the form of the yellow table ?

 

[MARK858]

The formulas Dave has posted work fine in your "yellow" table. Without the named ranges options as I haven't set them up...

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Lenght	Value	Lenght		Lenght			Data
2	1	2	27	15	53	28		11	5	6.5	7.7	3.02
3	3	3	29	16	55	29
4	6	4	31	17	57	30
5	8	5	33	18	59	31		Sum	correspond value
6	9	6	35	19	61	32		33	18
7	11	7	37	20	63	33
8	13	8	39	21	65	34
9	15	9	41	22	67	35
10	17	10	43	23	69	36
11	19	11	45	24	71	37
12	21	12	47	25	73	38
13	23	13	49	26	75	39
14	25	14	51	27	77	40
Sheet2

Cell Formulas
Range	Formula
H6	H6	=ROUND(SUM(H2:L2),0)
I6	I6	=CHOOSE(MATCH(H6,{1,27},1),VLOOKUP(H6,A2:B14,2,0),VLOOKUP(H6,C2:D14,2,0),VLOOKUP(H6,E2:F14,2,0))

or

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Lenght	Value	Lenght		Lenght			Data
2	1	2	27	15	53	28		11	5	6.5	7.7	3.02
3	3	3	29	16	55	29
4	6	4	31	17	57	30
5	8	5	33	18	59	31		Sum	correspond value
6	9	6	35	19	61	32		33.22	18
7	11	7	37	20	63	33
8	13	8	39	21	65	34
9	15	9	41	22	67	35
10	17	10	43	23	69	36
11	19	11	45	24	71	37
12	21	12	47	25	73	38
13	23	13	49	26	75	39
14	25	14	51	27	77	40
Sheet2

Cell Formulas
Range	Formula
H6	H6	=SUM(H2:L2)
I6	I6	=CHOOSE(MATCH(ROUND(H6,0),{1,27,53},1),VLOOKUP(ROUND(H6,0),A2:B14,2,0),VLOOKUP(ROUND(H6,0),C2:D14,2,0),VLOOKUP(ROUND(H6,0),E2:F14,2,0))

Edited to put in the separate formula boxes

 

[Dave Patton]

The post with your formatting.
N.B. You can copy the post to a clean sheet; use the icon below the f(x).

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Length	Value	Length		Length			Data
2	1	2	27	15	53	28		11	5	6.5	7.7	3.02
3	3	3	29	16	55	29
4	6	4	31	17	57	30		Sum	correspond value
5	8	5	33	18	59	31		33.22	18
6	9	6	35	19	61	32		33	18
7	11	7	37	20	63	33		33	18
8	13	8	39	21	65	34					#N/A
9	15	9	41	22	67	35					18
10	17	10	43	23	69	36					#N/A
11	19	11	45	24	71	37
12	21	12	47	25	73	38
13	23	13	49	26	75	39
14	25	14	51	27	77	40
4c

Cell Formulas
Range	Formula
H5	H5	=SUM(H2:M2)
I5	I5	=CHOOSE(MATCH(ROUND(H5,0),{1,27,53},1),VLOOKUP(ROUND(H5,0),A2:B14,2,0),VLOOKUP(ROUND(H5,0),C2:D14,2,0),VLOOKUP(ROUND(H5,0),E2:F14,2,0))
H6	H6	=ROUND(SUM(H2:L2),0)
I6	I6	=CHOOSE(MATCH(H6,{1,27,53},1),VLOOKUP(H6,A2:B14,2,0),VLOOKUP(H6,C2:D14,2,0),VLOOKUP(H6,E2:F14,2,0))
H7	H7	=ROUND(SUM(H2:L2),0)
I7	I7	=AGGREGATE(14,6,K8:K10,1)
K8	K8	=VLOOKUP(H7,A2:B14,2,0)
K9	K9	=VLOOKUP(H7,C2:D13,2,0)
K10	K10	=VLOOKUP(H7,E2:F14,2,0)

 

[hpbaxxter]

Thank you all.