Help formula in excel !!!

[Nguyen Anh Dung]

Hi All,
please help me formular for result in excel.
i have image as below. i want result as column F,G,H,I.
if E4= "Lớp G" then F4=1,G4=2,H4=3, I4=""
the same if E5=" Lớp H" then F5=4,G5=5,H5="", I5=""
the same if E6=" Lớp J" then F6=6,G6=7,H6=8, I6=9

Best regards,
Nguyen Anh Dung

 

[StephenCrump]

Definitely: F4=1,G4=2,H4=3 ?

And not: F4=2 (because B6 is "a"), G4=1 (because B5 ="b") ,H4=3 ?

 

[Nguyen Anh Dung]

Definitely: F4=1,G4=2,H4=3 ?

And not: F4=2 (because B6 is "a"), G4=1 (because B5 ="b") ,H4=3 ?

Dear StephenCrump,

yes, i wrong. i'm sorry!!!

if E4= "Lớp G" then F4=2,G4=1,H4=3, I4=""

Best regards,
Nguyen Anh Dung

 

[Habtest]

See below. If the source data is larger you'll need to adjust the cell reference "$C$33".

Book1
	A	B	C	D	E	F	G	H	I
1
2
3		Line	Jan			a	b	c	d
4		Lot G			Lot G	2	1	3
5		b	1		Lot N	19	18
6		a	2		Lot H	4	5
7		c	3		Lot F	10	11
8		Lot H			Lot D	15	17		16
9		a	4		Lot J	6	8	7	9
10		b	5		Lot C	13	14	12
11		Lot J			Lot E	20	21
12		a	6
13		c	7
14		b	8
15		d	9
16		Lot F
17		a	10
18		b	11
19		Lot C
20		c	12
21		a	13
22		b	14
23		Lot D
24		a	15
25		d	16
26		b	17
27		Lot N
28		b	18
29		a	19
30		Lot E
31		a	20
32		b	21
Sheet1

Cell Formulas
Range	Formula
F4:I11	F4	=IFERROR(VLOOKUP(F$3,OFFSET(INDIRECT("B"&MATCH($E4,$B:$B,0)+1,1),,,MATCH(TRUE,ISBLANK(INDIRECT("C"&MATCH($E4,$B:$B,0)+1,TRUE):$C$33),0)-1,2),2,0),"")

 

[Nguyen Anh Dung]

See below. If the source data is larger you'll need to adjust the cell reference "$C$33".

Book1
	A	B	C	D	E	F	G	H	I
1
2
3		Line	Jan			a	b	c	d
4		Lot G			Lot G	2	1	3
5		b	1		Lot N	19	18
6		a	2		Lot H	4	5
7		c	3		Lot F	10	11
8		Lot H			Lot D	15	17		16
9		a	4		Lot J	6	8	7	9
10		b	5		Lot C	13	14	12
11		Lot J			Lot E	20	21
12		a	6
13		c	7
14		b	8
15		d	9
16		Lot F
17		a	10
18		b	11
19		Lot C
20		c	12
21		a	13
22		b	14
23		Lot D
24		a	15
25		d	16
26		b	17
27		Lot N
28		b	18
29		a	19
30		Lot E
31		a	20
32		b	21
Sheet1

Cell Formulas
Range	Formula
F4:I11	F4	=IFERROR(VLOOKUP(F$3,OFFSET(INDIRECT("B"&MATCH($E4,$B:$B,0)+1,1),,,MATCH(TRUE,ISBLANK(INDIRECT("C"&MATCH($E4,$B:$B,0)+1,TRUE):$C$33),0)-1,2),2,0),"")

Hi Habtest,
Thanks you so much!!!
Best regards,
Nguyen Anh Dung