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

 

[jasonb75]

That is the example that you should have provided in post 1, although I am still not convinced that it is accurate!

Why is F28 -6 instead of -3?

Your result looks as if you're comparing Enough to Very Good instead of Enough to Good.

 

[muhammad susanto]

That is the example that you should have provided in post 1, although I am still not convinced that it is accurate!

Why is F28 -6 instead of -3?

Your result looks as if you're comparing Enough to Very Good instead of Enough to Good.

'YOU'RE RIGHT IN F28 SHOULD BE -3. I'M WRONG TYPING

 

[jasonb75]

In E19 based on your last example.

=IF(SUMIFS($D$4:$D$15,$B$4:$B$15,B19,$C$4:$C$15,C19)=SUMIFS($D$4:$D$15,$B$4:$B$15,B19,$C$4:$C$15,D19),0,-SUMIFS($E$4:$E$15,$B$4:$B$15,B19,$C$4:$C$15,C19)-SUMIFS($F$4:$F$15,$B$4:$B$15,B19,$C$4:$C$15,D19))

 

[muhammad susanto]

In E19 based on your last example.

=IF(SUMIFS($D$4:$D$15,$B$4:$B$15,B19,$C$4:$C$15,C19)=SUMIFS($D$4:$D$15,$B$4:$B$15,B19,$C$4:$C$15,D19),0,-SUMIFS($E$4:$E$15,$B$4:$B$15,B19,$C$4:$C$15,C19)-SUMIFS($F$4:$F$15,$B$4:$B$15,B19,$C$4:$C$15,D19))

HI jasonb75....
Thank You very much!!! Worked well..A long time i am waiting this formula...
You're great!!!!

thank you, thank you.....