Spreadsheet Function: XLOOKUP()

Definition:

To look for a value and return corresponding value(s).

---

Command Syntax:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

• Refer to:https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Reference cells of lookup_array cannot exactly equal to reference cells of return_array.
• Reference cells of lookup_array can only be at the same column(VLOOKUP) or the same row(HLOOKUP).

---

lookup_value

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the exact mark 98, searching start from the first cell.)

	F	G	H
1	Highest mark	Name
2		=XLOOKUP(98,D2:D7,A2:A7)
3	Lowest mark	Name
4
5

Output:

	F	G	H
1	Highest mark	Name
2		Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			Chris
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the exam mark of Chris Wong, searching start from the first cell.)

	F	G	H
1	Highest mark	Name
2	=XLOOKUP(G2,A2:A7,D2:D7)	Chris Wong
3	Lowest mark	Name
4
5

Output:

	F	G	H
1	Highest mark	Name
2	98	Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the lowest mark, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39			=XLOOKUP(MIN(D2:D7),D2:D7,A2:A7)
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2
3	Lowest mark	Name
4		Nico Lin
5

Worksheet Sheet1

	A	B	C	D	E
1	Name	Class	Class no	Mark
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5		Nico Lin
6

Input:
(Find the exam mark of Nico Lin, searching start from the first cell.)

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5	=XLOOKUP(B5,Sheet1!A2:Sheet1!A7,Sheet1!D2:D7)	Nico Lin
6

Output:

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5	12	Nico Lin
6

Worksheet Sheet2

	A	B	C	D	E	F	G	H	I	J
1	Club no.	1	2	3	4	5		Search No.	Club Name
2	Club Name	ABC Club	RTP Club	ICTISFUN Club	Science Society	Sports Club		2
3

Input:
(Find the club name who's club no. equals to the content of cell H2.)

	H	I	J
1	Search No.	Club Name
2	2	=XLOOKUP(H2,B1:F1,B2:F2)
3

Output:

	H	I	J
1	Search No.	Club Name
2	2	RTP Club
3

---

lookup_array

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the exact mark 98, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			=XLOOKUP(98,D2:D7,A2:A7)
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2		Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			Chris
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the exam mark of Chris Wong, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98		=XLOOKUP(G2,A2:A7,D2:D7)	Chris
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2	98	Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the lowest mark, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39			=XLOOKUP(MIN(D2:D7),D2:D7,A2:A7)
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2
3	Lowest mark	Name
4		Nico Lin
5

Worksheet Sheet1

	A	B	C	D	E
1	Name	Class	Class no	Mark
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5		Nico Lin
6

Input:
(Find the exam mark of Nico Lin, searching start from the first cell.)

Worksheet Sheet1

	A	B	C	D	E
1	Name	Class	Class no	Mark
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5	=XLOOKUP(B5,Sheet1!A2:Sheet1!A7,Sheet1!D2:D7)	Nico Lin
6

Output:

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5	12	Nico Lin
6

Worksheet Sheet2

	A	B	C	D	E	F	G	H	I	J
1	Club no.	1	2	3	4	5		Search No.	Club Name
2	Club Name	ABC Club	RTP Club	ICTISFUN Club	Science Society	Sports Club		2
3

Input:
(Find the club name who's club no. equals to the content of cell H2.)

	A	B	C	D	E	F	G	H	I	J
1	Club no.	1	2	3	4	5		Search No.	Club Name
2	Club Name	ABC Club	RTP Club	ICTISFUN Club	Science Society	Sports Club		2	=XLOOKUP(H2,B1:F1,B2:F2)
3

Output:

	H	I	J
1	Search No.	Club Name
2	2	RTP Club
3

---

return_array

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the exact mark 98, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			=XLOOKUP(98,D2:D7,A2:A7)
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2		Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			Chris
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the exam mark of Chris Wong, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98		=XLOOKUP(G2,A2:A7,D2:D7)	Chris
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2	98	Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the lowest mark, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39			=XLOOKUP(MIN(D2:D7),D2:D7,A2:A7)
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2
3	Lowest mark	Name
4		Nico Lin
5

Worksheet Sheet1

	A	B	C	D	E
1	Name	Class	Class no	Mark
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5		Nico Lin
6

Input:
(Find the exam mark of Nico Lin, searching start from the first cell.)

Worksheet Sheet1

	A	B	C	D	E
1	Name	Class	Class no	Mark
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5	=XLOOKUP(B5,Sheet1!A2:Sheet1!A7,Sheet1!D2:D7)	Nico Lin
6

Output:

Worksheet Sheet3

	A	B	C
1	Highest mark	Name
2
3
4	Lowest mark	Name
5	12	Nico Lin
6

Worksheet Sheet2

	A	B	C	D	E	F	G	H	I	J
1	Club no.	1	2	3	4	5		Search No.	Club Name
2	Club Name	ABC Club	RTP Club	ICTISFUN Club	Science Society	Sports Club		2
3

Input:
(Find the club name who's club no. equals to the content of cell H2.)

	A	B	C	D	E	F	G	H	I	J
1	Club no.	1	2	3	4	5		Search No.	Club Name
2	Club Name	ABC Club	RTP Club	ICTISFUN Club	Science Society	Sports Club		2	=XLOOKUP(H2,B1:F1,B2:F2)
3

Output:

	H	I	J
1	Search No.	Club Name
2	2	RTP Club
3

---

[if_not_found]

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the exact mark 100, searching start from the first cell.)

	F	G	H
1	Highest mark	Name
2		=XLOOKUP(100,D2:D7,A2:A7)
3	Lowest mark	Name
4
5

Output:

	F	G	H
1	Highest mark	Name
2		#N/A
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Exam mark	Name
2	Chris Wong	1A	5	98			Mary
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the exam mark of student who's first name is Mary, searching start from the first cell.)

	F	G	H
1	Exam mark	Name
2	=XLOOKUP(G2&"*",A2:A7,D2:D7,"error",2)	Mary
3

Output:

	F	G	H
1	Exam mark	Name
2	error	Mary
3

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the exact mark 100, otherwise show Amy Chan, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			=XLOOKUP(100,D2:D7,A2:A7,A4)
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2		Amy Chan
3	Lowest mark	Name
4
5

---

[match_mode]

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the exact mark 98, searching start from the first cell.)

	F	G	H
1	Highest mark	Name
2		=XLOOKUP(98,D2:D7,A2:A7,,0)
3	Lowest mark	Name
4
5
6
7

Output:

	F	G	H
1	Highest mark	Name
2		Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Mark	Name
2	Chris Wong	1A	5	98		25
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the mark 25 or nearly larger, searching start from the first cell.)

	F	G	H
1	Mark	Name
2	25	=XLOOKUP(F2,D2:D7,A2:A7,,1)
3

Output:

	F	G	H
1	Mark	Name
2	25	Kitty Leung
3

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Mark	Name
2	Chris Wong	1A	5	98		25
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the mark 25 or nearly smaller, searching start from the first cell.)

	F	G	H
1	Mark	Name
2	25	=XLOOKUP(F2,D2:D7,A2:A7,,-1)
3

Output:

	F	G	H
1	Mark	Name
2	25	Nico Lin
3

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Exam mark	Last name
2	Chris Wong	1A	5	98			Cheung
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the exam mark of student who's last name is Cheung, searching start from the first cell.)

	F	G	H
1	Exam mark	Name
2	=XLOOKUP("*"&G2,A2:A7,D2:D7,,2)	Cheung
3

Output:

	F	G	H
1	Exam mark	Name
2	98	Cheung
3

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Mark	Name
2	Chris Wong	1A	5	98		10
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the mark 10 or nearly smaller, searching start from the first cell.)

	F	G	H
1	Mark	Name
2	10	=XLOOKUP(F2,D2:D7,A2:A7,,-1)
3

Output:

	F	G	H
1	Mark	Name
2	10	#N/A
3

---

[search_mode]

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the exact mark 98, searching start from the first cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			=XLOOKUP(98,D2:D7,A2:A7,,,1)
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2		Chris Wong
3	Lowest mark	Name
4
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39		12
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the lowest mark, searching start from the last cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12		Lowest mark	Name
4	Amy Chan	2D	12	39		12	=XLOOKUP(MIN(D2:D7),D2:D7,A2:A7,,,-1)
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
3	Lowest mark	Name
4	12	Ken Chan
5

Worksheet Sheet1

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Input:
(Find the name of the student who gets the mark 100 or nearly smaller, searching start from the last cell.)

	A	B	C	D	E	F	G	H
1	Name	Class	Class no	Mark		Highest mark	Name
2	Chris Wong	1A	5	98			=XLOOKUP(100,D2:D7,A2:A7,,-1,-1)
3	Nico Lin	1C	7	12
4	Amy Chan	2D	12	39
5	Ken Chan	3A	1	12
6	Frank Cheung	3A	26	98
7	Kitty Leung	2A	9	27

Output:

	F	G	H
1	Highest mark	Name
2		Frank Cheung
3

Worksheet Sheet4

	A	B	C	D	E	F
1	Group Number	Name		Group Number	Find Name
2	1	Jimmy		4
3	3	Tom
4	4	Rachael
5	4	Gary
6	4	Emma
7	4	Herry
8	5	Jayden
9	6	Wincy
10

Input:
(Find the name of the student who is in Group 4 listed at the first place of the sorted table.)

	A	B	C	D	E	F
1	Group Number	Name		Group Number	Find Name
2	1	Jimmy		4	=XLOOKUP(D2,A2:A9,B2:B9,,,2)
3	3	Tom
4	4	Rachael
5	4	Gary
6	4	Emma
7	4	Herry
8	5	Jayden
9	6	Wincy
10

Output:

	D	E	F
1	Group Number	Find Name
2	4	Rachael
3

Worksheet Sheet4

	A	B	C	D	E	F
1	Group Number	Name		Group Number	Find Name
2	1	Jimmy		4
3	3	Tom
4	4	Rachael
5	4	Gary
6	4	Emma
7	4	Herry
8	5	Jayden
9	6	Wincy
10

Input:
(Find the name of the student who is in Group 4 listed at the last place of the sorted table.)

	A	B	C	D	E	F
1	Group Number	Name		Group Number	Find Name
2	1	Jimmy		4	=XLOOKUP(D2,A2:A9,B2:B9,,,-2)
3	3	Tom
4	4	Rachael
5	4	Gary
6	4	Emma
7	4	Herry
8	5	Jayden
9	6	Wincy
10

Output:

	D	E	F
1	Group Number	Find Name
2	4	Herry
3

---

‘A mixture of HLOOKUP and VLOOKUP but not exactly the same.’By secondary 6 student