index and match failed

[Jongnj87]

Hello good day all,

I would like to enquiry on index and match formula which I use on my data failed. I been looking on video but still unable to get the correct formula. I have a data and a sheet for return.

	A	B	C	D	E	F	G
1	ID	Name	Gender	Result	Grade	Subject	Remarks
2	T001	Candy	Female	58%	Just Pass	English
3	T002	Fiona	Female	76%	Pass	English
4	T003	Jeff	Male	38%	Fail	English
5	T004	John	Male	93%	Pass	English
6	T005	Matt	Male	69%	Just Pass	English

On the other tab(Sheet), I need to have the return to submit to my supervisor

	A	B	C	D	E
1	ID	Name	Gender	Result
2
3
4

Cell A2 =INDEX('Full Namelist'!,1,MATCH("Just Pass",'Full Namelist'!$E$1:$E$6,0))
Cell B2 =Vlookup(A2, 'Full Namelist'!$A$2:$G$6, 1, False)

I tried to drag down cell A2 to A4 it came out all same ID.

Anyone please guide me on this.

Thank You.

 

[Sufiyan97]

Try

you need to type ID number manually on another sheet

Full Namelist

Book1
	A	B	C	D	E	F	G	H
1		A	B	C	D	E	F	G
2	1	ID	Name	Gender	Result	Grade	Subject	Remarks
3	2	T001	Candy	Female	58%	Just Pass	English
4	3	T002	Fiona	Female	76%	Pass	English
5	4	T003	Jeff	Male	38%	Fail	English
6	5	T004	John	Male	93%	Pass	English
7	6	T005	Matt	Male	69%	Just Pass	English
8		T006	Rock	Male	70%	Pass	English
9
Full Namelist

Summary

Book1
	A	B	C	D	E	F	G	H	I
1		A	B	C	D	E
2	1	ID	Name	Gender	Result	Grade	Subject	Remarks
3	2	T001	Candy	Female	58%	Just Pass	English	0
4	3	T002	Fiona	Female	76%	Pass	English	0
5	4	T003	Jeff	Male	38%	Fail	English	0
6	5	T004	John	Male	93%	Pass	English	0
7	6	T005	Matt	Male	69%	Just Pass	English	0
8		T006	Rock	Male	70%	Pass	English	0
9
10
Summary

Cell Formulas
Range	Formula
C3:C8	C3	=INDEX('Full Namelist'!C:C,MATCH(B3,'Full Namelist'!B:B,0),)
D3:D8	D3	=INDEX('Full Namelist'!D:D,MATCH(B3,'Full Namelist'!B:B,0),)
E3:E8	E3	=INDEX('Full Namelist'!E:E,MATCH(B3,'Full Namelist'!B:B,0),)
F3:F8	F3	=INDEX('Full Namelist'!F:F,MATCH(B3,'Full Namelist'!B:B,0),)
G3:G8	G3	=INDEX('Full Namelist'!G:G,MATCH(B3,'Full Namelist'!B:B,0),)
H3:H8	H3	=INDEX('Full Namelist'!H:H,MATCH(B3,'Full Namelist'!B:B,0),)

 

[Jongnj87]

Try

you need to type ID number manually on another sheet

Full Namelist

Book1
	A	B	C	D	E	F	G	H
1		A	B	C	D	E	F	G
2	1	ID	Name	Gender	Result	Grade	Subject	Remarks
3	2	T001	Candy	Female	58%	Just Pass	English
4	3	T002	Fiona	Female	76%	Pass	English
5	4	T003	Jeff	Male	38%	Fail	English
6	5	T004	John	Male	93%	Pass	English
7	6	T005	Matt	Male	69%	Just Pass	English
8		T006	Rock	Male	70%	Pass	English
9
Full Namelist

Summary

Book1
	A	B	C	D	E	F	G	H	I
1		A	B	C	D	E
2	1	ID	Name	Gender	Result	Grade	Subject	Remarks
3	2	T001	Candy	Female	58%	Just Pass	English	0
4	3	T002	Fiona	Female	76%	Pass	English	0
5	4	T003	Jeff	Male	38%	Fail	English	0
6	5	T004	John	Male	93%	Pass	English	0
7	6	T005	Matt	Male	69%	Just Pass	English	0
8		T006	Rock	Male	70%	Pass	English	0
9
10
Summary

Cell Formulas
Range	Formula
C3:C8	C3	=INDEX('Full Namelist'!C:C,MATCH(B3,'Full Namelist'!B:B,0),)
D3:D8	D3	=INDEX('Full Namelist'!D:D,MATCH(B3,'Full Namelist'!B:B,0),)
E3:E8	E3	=INDEX('Full Namelist'!E:E,MATCH(B3,'Full Namelist'!B:B,0),)
F3:F8	F3	=INDEX('Full Namelist'!F:F,MATCH(B3,'Full Namelist'!B:B,0),)
G3:G8	G3	=INDEX('Full Namelist'!G:G,MATCH(B3,'Full Namelist'!B:B,0),)
H3:H8	H3	=INDEX('Full Namelist'!H:H,MATCH(B3,'Full Namelist'!B:B,0),)

Hello, thanks.

The issue is not all my staff know how to do filter and do it manually.

That why, for that sheet it just the "Just Pass" base on that it will auto come out the ID easy for my staff.

Other tabs will have it own "Pass" or "Fail". By doing so it will be easy for my staff to use.

 

[alansidman]

Are you saying you only want to see the records in which Column E contains the value "Just Pass?"

 

[Jongnj87]

Are you saying you only want to see the records in which Column E contains the value "Just Pass?"

Hi Sir,

Yes.

Which mean in other tab/Sheet, I want to find the ID of the student in Column A base on the record in Data Column E.

With that the

 

[Peter_SSs]

Like this?

Jongnj87.xlsm
	A	B	C	D	E	F	G
1	ID	Name	Gender	Result	Grade	Subject	Remarks
2	T001	Candy	Female	58%	Just Pass	English
3	T002	Fiona	Female	76%	Pass	English
4	T003	Jeff	Male	38%	Fail	English
5	T004	John	Male	93%	Pass	English
6	T005	Matt	Male	69%	Just Pass	English
7
Full Namelist

(No need to copy/drag this formula anywhere)

Jongnj87.xlsm
	A	B	C	D
1	ID	Name	Gender	Result
2	T001	Candy	Female	58%
3	T005	Matt	Male	69%
4
Sheet2

Cell Formulas
Range	Formula
A2:D3	A2	=FILTER('Full Namelist'!A2:D100,'Full Namelist'!E2:E100="Just Pass","")
Dynamic array formulas.

 

[Jongnj87]

Like this?

Jongnj87.xlsm
	A	B	C	D	E	F	G
1	ID	Name	Gender	Result	Grade	Subject	Remarks
2	T001	Candy	Female	58%	Just Pass	English
3	T002	Fiona	Female	76%	Pass	English
4	T003	Jeff	Male	38%	Fail	English
5	T004	John	Male	93%	Pass	English
6	T005	Matt	Male	69%	Just Pass	English
7
Full Namelist

(No need to copy/drag this formula anywhere)

Jongnj87.xlsm
	A	B	C	D
1	ID	Name	Gender	Result
2	T001	Candy	Female	58%
3	T005	Matt	Male	69%
4
Sheet2

Cell Formulas
Range	Formula
A2:D3	A2	=FILTER('Full Namelist'!A2:D100,'Full Namelist'!E2:E100="Just Pass","")
Dynamic array formulas.

Hello Sir,

Thank you so much. I learn extra formula today.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.