Lookup data then fill mark "V" or "-" based on criteria

[muhammad susanto]

hello all...

how to get or lookup data then fill mark V or - with same criteria name and description
my target in cell H12 till J15 with dropdown list name in cell H10 as reference name
it's 3 criteria very good, good, bad.
here the layout

rumus rapot tk.xlsx
	G	H	I	J	K
1	Description	Names
2		Farah	Tika	Dyla
3	Mango	Very good	Good	Bad
4	Orange	Very good	Good	Bad
5	Banana	Good	Good	Bad
6	Grape	Bad	Bad	Very good
7	Papaya	Very good	Bad	Very good
8
9	Target/expected desired
10	Name ---->>	Farah
11	Description	Bad	Good	Very good
12	Mango	-	-	V
13	Orange	-	-	V
14	Banana	-	V	-
15	Grape	V	-	-
16	Papaya
Sheet1

for this case , please use excel 2010 or 2013 formula

any help greatly appreciated..

sst

 

[Cubist]

Try:

Book1
	G	H	I	J	K
1	Description	Names
2		Farah	Tika	Dyla
3	Mango	Very good	Good	Bad
4	Orange	Very good	Good	Bad
5	Banana	Good	Good	Bad
6	Grape	Bad	Bad	Very good
7	Papaya	Very good	Bad	Very good
8
9	Target/expected desired
10	Name ---->>	Farah
11	Description	Bad	Good	Very good
12	Mango	-	-	V
13	Orange	-	-	V
14	Banana	-	V	-
15	Grape	V	-	-
16	Papaya	-	-	V
Sheet6

Cell Formulas
Range	Formula
H12:J16	H12	=IF(INDEX($H$3:$J$7,MATCH($G12,$G$3:$G$7,0),MATCH($H$10,$H$2:$J$2,0))=H$11,"V","-")

 

[muhammad susanto]

hi Cubist, thank you. working well

 

[Cubist]

You're welcome.

 

[muhammad susanto]

hi cubist , sorry

rumus rapot tk.xlsx
	G	H	I	J	K	L	M
1	Period of	Half Year 1			Half Year 2
2	Description	Names
3		Farah	Tika	Dyla	Farah	Tika	Dyla
4	Mango	Very good	Good	Good	Very good	Good	Good
5	Orange	Very good	Good	Bad	Very good	Good	Bad
6	Banana	Good	Good	Bad	Good	Good	Bad
7	Grape	Bad	Bad	Very good	Bad	Bad	Very good
8	Papaya	Very good	Bad	Very good	Very good	Bad	Very good
9
10	Target/expected desired
11	Period of	Half Year 1
12	Name ---->>	Farah
13	Description	Bad	Good	Very good
14	Mango	-	-	V
15	Orange	-	-	V
16	Banana	-	V	-
17	Grape	V	-	-
18	Papaya	-	-	V
Sheet1

Cell Formulas
Range	Formula
H14:J18	H14	=IF(INDEX($H$4:$J$8,MATCH($G14,$G$4:$G$8,0),MATCH($H$12,$H$3:$J$3,0))=H$13,"V","-")

Cells with Data Validation
Cell	Allow	Criteria
H12	List	=$N$4:$N$6

how to solve this if i want add "period of ..." in cell H11? how to lookup if period in Half Year 1 or Half Year 2?
in cell H11 as reference too with dropdown list

 

[Cubist]

Assuming the names will be in the same order. Try:

Book1
	G	H	I	J	K	L	M
1	Period of	Half Year 1			Half Year 2
2	Description	Names
3		Farah	Tika	Dyla	Farah	Tika	Dyla
4	Mango	Very good	Good	Good	Very good	Good	Good
5	Orange	Very good	Good	Bad	Very good	Good	Bad
6	Banana	Good	Good	Bad	Very good	Good	Bad
7	Grape	Bad	Bad	Very good	Very good	Bad	Very good
8	Papaya	Very good	Bad	Very good	Very good	Bad	Very good
9
10	Target/expected desired
11	Period of	Half Year 1
12	Name ---->>	Farah
13	Description	Bad	Good	Very good
14	Mango	-	-	V
15	Orange	-	-	V
16	Banana	-	V	-
17	Grape	V	-	-
18	Papaya	-	-	V
Sheet7

Cell Formulas
Range	Formula
H14:J18	H14	=IF(INDEX(IF($H$11=$H$1,$H$4:$J$8,$K$4:$M$8),MATCH($G14,$G$4:$G$8,0),MATCH($H$12,$H$3:$J$3,0))=H$13,"V","-")

 

[muhammad susanto]

hi..

i think not work, for farah in Half Year I, not correct result

 

[Cubist]

Can you show how you applied the formula?

 

[muhammad susanto]

rumus rapot tk.xlsx
	G	H	I	J	K	L	M
1	Period of	Semester I			Semester II
2	Description	Names
3		Farah	Tika	Dyla	Farah	Tika	Dyla
4	Mango	Bad	Good	Good	Good	Good	Bad
5	Orange	Bad	Good	Bad	Good	Good	Good
6	Banana	Good	Good	Bad	Very good	Good	Good
7	Grape	Bad	Bad	Very good	Bad	Bad	Very good
8	Papaya	Very good	Bad	Very good	Bad	Bad	Bad
9
10	Target/expected desired
11	Period of	Semester I
12	Name ---->>	Farah
13	Description	Bad	Good	Very good
14	Mango	V	V	-
15	Orange	-	V	-
16	Banana	-	-	V
17	Grape	V	-	-
18	Papaya	V	-	-
Sheet1

Cell Formulas
Range	Formula
H14:J18	H14	=IF(INDEX(IF(H11=$H$1,$H$4:$J$8,$K$4:$M$8),MATCH($G14,$G$4:$G$8,0),MATCH($H$12,$H$3:$J$3,0))=H$13,"V","-")

Cells with Data Validation
Cell	Allow	Criteria
H11	List	=$O$4:$O$5
H12	List	=$N$4:$N$6

for Farah , orange bad shoud be marked 'V"

 

[Cubist]

You need to lock H11.

=IF(INDEX(IF($H$11=$H$1,$H$4:$J$8,$K$4:$M$8),MATCH($G14,$G$4:$G$8,0),MATCH($H$12,$H$3:$J$3,0))=H$13,"V","-")