Lookup Index Based on Comparing "Grade"

[muhammad susanto]

hi all..

i have problem about compare criteria then filling data index with 3 option 0%,negative% or positive % based on criteria
the table like the below:

index compare 2020.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Calculation Analysis											Class	Grade	Index
6	Description	Objek Target	P-1	Index	P-2	Index		do not remove			Machine Condt	Very Good	1	5%
7								P-1	P-2			Good	2	10%
8												Worst	3	25%
9	Machine Condt	Very Good	Good	-25,0%	Good	+10,0%		FALSE	TRUE
10
11				if red highlighted the cell is contains data wrong		correct cause "very good" in grade 1 then "Good" in grade 2, number index must postive percentage
12		note
13	1	"Very good" vs "good" the result is MUST Positive percentage cause "Very Good" in grade 1 and "Good" in grade 2	Worst Vs Good, the index is MUST Negative cause Worst in grade 3 and Good in grade 2
14	2	if the description same e.g. Very Good vs Very Good , data index MUST 0%
ok

Cell Formulas
Range	Formula
H9	H9	=SIGN(D9)=SIGN(VLOOKUP(INDEX($A$8:$F$9,MATCH($A9,$A$8:$A$9,0),MATCH(C$6,$A$6:$F$6,0)),$L$6:$M$8,2,0)-VLOOKUP(INDEX($B$8:$B$9,MATCH($A9,$A$8:$A$9,0)),$L$6:$M$8,2,0))
I9	I9	=SIGN(F9)=SIGN(VLOOKUP(INDEX($A$8:$F$9,MATCH($A9,$A$8:$A$9,0),MATCH(E$6,$A$6:$F$6,0)),$L$6:$M$8,2,0)-VLOOKUP(INDEX($B$8:$B$9,MATCH($A9,$A$8:$A$9,0)),$L$6:$M$8,2,0))

my target in cell D9 and F9, how to fill (lookup) data index based on 3 criteria above
any help, thanks in advance..

sst.

 

[CA_Punit]

Sorry could you please explain the criteria in details. I didnot get how you got 25% in D9

 

[muhammad susanto]

Sorry could you please explain the criteria in details. I didnot get how you got 25% in D9

25% or 10% is given (manual) avaliable/based on data index in column N

 

[CA_Punit]

So is 25% the desired result??

Is it is so considering the data ranges for Machine condition good it should result in 10% instead of 25%.

I am sorry i could not figure out the criteria

 

[muhammad susanto]

yes you 're right , my expected result col D9 & F9...
lookup based on grade/class, remember based on comparing for each criteria (Good vs Very Good, or Good vs worst, etc....)

 

[muhammad susanto]

my similar problem in previous posting but i can't found my posting again
but i remember Peters_Ss have help me out...
may be this complex problem then i think we need adding helper column

Machine Condt	Class/Level	grade	Index (manual given)	Index (manual given)
	Very good	1	5%	-5%
	good	2	10%	-10%
	worst	3	20%	-20%

 

[muhammad susanto]

i hope this my attachment file can help

test_adjustment.xlsx

drive.google.com

please, download it.

 

[Fluff]

Cross posted.

Please supply the links as per board policy. Thanks

 

[muhammad susanto]

sorry Fluff...

this a cross posted Vlookup With 3 Number Type Based On Criteria

 

[muhammad susanto]

i hope this make clear
(from attachment file)

Based on index number in cell Q14 & R14 and col."rating/grade" in col.M10 down
comparing data in B14 vs C14 (col."Objek" vs "P-1"), shoud be like this
Very good vs Bad ----the result is 5%
Very good vs Very good --the result is 0% (same text)
Very good vs good --the result is 15%

otherwise (comparing data B14 vs E14 (col."objek" vs "P-2"), shoudl be:
Enough vs Good - the result is -11%
Enough vs enough - the result is 0%
Enough vs bad - the result is 11%