Vlookup Index Based On Level Number Criteria

[muhammad susanto]

hi all...

how to make vlookup index based on level number criteria:
"Good" in cell C11 as basis

vlookup compare.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
3	data	criteria	number of level	index1	index2
4	Orange Juice	Very good	1	0%	0%
5	Orange Juice	Good	2	2%	-2%
6	Orange Juice	Enough	3	4%	-4%
7	Orange Juice	Bad	4	6%	-6%
8	Orange Juice	Worst	5	8%	-8%
9
10	Expected Result		Compare-1		Compare-2		Compare-3
11	Orange Juice	Good	Very good	-2%	Good	0%	Bad	6%
12				my target		my target		my target
13
14
15
16
17
18
19
20		Note :
21		Good vs Very Good (cause Very Good is higher level than Good, based on number of level so put index is -2%.
22		Good Vs Good (in same level 2 vs 2, based on numbe rof level so put index is 0%
23		Good vs Bad (cause Good is higher level than Bad , based on number of level so put index is 6%.
Sheet1

my target in cell E12, G12 & I12...
this attachment file for more easy understanding

vlookup compare.xlsx

drive.google.com

thank for your helping.

.sst

 

[muhammad susanto]

maybe you can use combine this formula
Sign, Index, Match, Vlookup..

 

[muhammad susanto]

correction, sorry my target in cell E11, G11 & I11.

 

[jasonb75]

This needs some different examples to aid with the explanation, the solution is probably quite simple but it is unclear how you get the results that you expect.

 

[muhammad susanto]

hi Jasonb75, thank for your suggestion. & correction.
i'm using Excel 2013 and Excel 2016..
I hope you have solution

 

[jasonb75]

thank for your suggestion.

I hope you have solution

I suggested additional examples, without them no hope of a solution.

 

[muhammad susanto]

hi..i have additional example..

vlookup compare.xlsx
	B	C	D	E	F	G	H	I
3	data	criteria	number of level	index1	index2
4	Orange Juice	Very good	1	0%	0%
5	Orange Juice	Good	2	2%	-2%
6	Orange Juice	Enough	3	4%	-4%
7	Orange Juice	Bad	4	6%	-6%
8	Orange Juice	Worst	5	8%	-8%
9
10	Expected Result		Compare-1		Compare-2		Compare-3
11	Orange Juice	Very good	Very good	0%	Enough	4%	Bad	6%
12	Orange Juice	Good	Good	0%	Very good	-2%	Very good	-2%
13	Orange Juice	Enough	Enough	0%	Worst	8%	Good	-2%
14	Orange Juice	Bad	Bad	0%	Bad	0%	Very good	-6%
15	Orange Juice	Worst	Worst	0%	Enough	-4%	Worst	0%
16
17		Note :
18	1	Good vs Very Good (cause Very Good is higher level than Good, based on number of level so put index is -2%.
19	2	Good Vs Good (in same level 2 vs 2, based on numbe rof level so put index is 0%
20	3	Good vs Bad (cause Good is higher level than Bad , based on number of level so put index is 6%.
21	4	Column C11 till C15 as basis (clue)
22	5	my target in column E11-E15; col. G11-G15 and col.I11-I15
Sheet1

 

[jasonb75]

Is this what you want? One of your example results doesn't appear to match the rest.

Book3
	B	C	D	E	F	G	H	I
3	data	criteria	number of level	index1	index2
4	Orange Juice	Very good	1	0%	0%
5	Orange Juice	Good	2	2%	-2%
6	Orange Juice	Enough	3	4%	-4%
7	Orange Juice	Bad	4	6%	-6%
8	Orange Juice	Worst	5	8%	-8%
9
10	Expected Result		Compare-1		Compare-2		Compare-3
11	Orange Juice	Very good	Very good	0%	Enough	4%	Bad	6%
12	Orange Juice	Good	Good	0%	Very good	-2%	Very good	-2%
13	Orange Juice	Enough	Enough	0%	Worst	4%	Good	-2%
14	Orange Juice	Bad	Bad	0%	Bad	0%	Very good	-6%
15	Orange Juice	Worst	Worst	0%	Enough	-4%	Worst	0%
Sheet1

Cell Formulas
Range	Formula
I11:I15,G11:G15,E11:E15	E11	=-VLOOKUP($C11,$C$4:$E$8,3,0)-VLOOKUP(D11,$C$4:$F$8,4,0)

 

[muhammad susanto]

Is this what you want? One of your example results doesn't appear to match the rest.

Book3
	B	C	D	E	F	G	H	I
3	data	criteria	number of level	index1	index2
4	Orange Juice	Very good	1	0%	0%
5	Orange Juice	Good	2	2%	-2%
6	Orange Juice	Enough	3	4%	-4%
7	Orange Juice	Bad	4	6%	-6%
8	Orange Juice	Worst	5	8%	-8%
9
10	Expected Result		Compare-1		Compare-2		Compare-3
11	Orange Juice	Very good	Very good	0%	Enough	4%	Bad	6%
12	Orange Juice	Good	Good	0%	Very good	-2%	Very good	-2%
13	Orange Juice	Enough	Enough	0%	Worst	4%	Good	-2%
14	Orange Juice	Bad	Bad	0%	Bad	0%	Very good	-6%
15	Orange Juice	Worst	Worst	0%	Enough	-4%	Worst	0%
Sheet1

Cell Formulas
Range	Formula
I11:I15,G11:G15,E11:E15	E11	=-VLOOKUP($C11,$C$4:$E$8,3,0)-VLOOKUP(D11,$C$4:$F$8,4,0)

hi jasonb75...
thank you but your formula not fully worked. you're right my sample not fully representative my problem..
i just adding my sample with combination & alternative, adding data and adding in New Level & New Index

TEST_ADJUSTMENT CORRECT OK.xlsx
	B	C	D	E	F	G
3	data	Basis	number of level	index1	index2
4	Orange Juice	Very good	1	0,0	0,0
5	Orange Juice	Good	2	2,0	-2,0
6	Orange Juice	Enough	3	4,0	-4,0
7	Orange Juice	Bad	4	6,0	-6,0
8	Orange Juice	Worst	5	8,0	-8,0
9			in different index & level
10	Aple	Very good	1	1,0	-1,0
11	Aple	Good	1	2,0	-2,0
12
13	Banana	Very good	1	0,0	0,0
14	Banana	Good	1	3,0	-3,0
15	Banana	Enough	2	6,0	-6,0
16
17				Expected result
18	data	Basis	Compare-1	index	SHOULD BE
19	Orange Juice	Very good	Very good	-6,0	0,0	FALSE
20	Orange Juice	Enough	Enough	0,0	0,0	TRUE
21	Orange Juice	Good	Bad	4,0	4,0	TRUE
22	Orange Juice	Worst	Very good	-8,0	8,0	TRUE
23	Aple	Good	Good	?	0,0	cause in the same level (1 and 1)
24	Aple	Good	Very good	?	0,0	cause in the same level
25	Banana	Enough	Enough	?	0,0	cause in the same level
26	Banana	Very good	Enough	?	6,0	cause in diferent level
27	Banana	Good	Very good	?	0,0	cause in the same level
28	Banana	Enough	Good	?	-6,0	cause in diferent level
Sheet2

Cell Formulas
Range	Formula
E19	E19	=-VLOOKUP($C19,$C$4:$E$8,3)-VLOOKUP(D19,$C$4:$F$8,4,0)
E20:E22	E20	=-VLOOKUP($C20,$C$4:$E$8,3,0)-VLOOKUP(D20,$C$4:$F$8,4,0)

i hope you would help me out..

 

[muhammad susanto]

NOTE : CORRECTION
IN CELL F22 -- SHOULD BE -8