EXTRACT ALL DATA ROWS OF A SPECIFIC EMPLOYEE FROM A TABLE

[SAQIB_4414]

Dear Experts,
i have a Employee Table having "Name" , " Month" and "Salary" Columns.
i want that , when i enter Name of an employee , it Fetch all of Data Rows of him (in new sheet / place in that sheet) .
i've tried Lookup, but it don't work..


Thanks in Advance..

FOR REFERENCE

0722 SSS.xlsx
	H	I	J
953	SOURCE DATA
954	EMPLOYEE NAME	SALARY MONTH	SALARY
955	SAQIB	Jan-22	120,000
956	SAQIB	Feb-20	120,000
957	SAQIB	Mar-22	120,000
958	RAZA	Jan-22	110,000
959	RAZA	Feb-22	110,000
960	RAZA	Mar-22	110,000
961	SAQIB	Apr-22	130,000
962	SAQIB	May-22	130,000
963	SAQIB	Jun-22	130,000
964	RAZA	Apr-22	120,000
965	RAZA	May-22	120,000
966	RAZA	Jun-22	120,000
967
968
969	RESULT
970	ENTER NAME	SAQIB
971	SAQIB	Jan-22	120,000
972	SAQIB	Feb-20	120,000
973	SAQIB	Mar-22	120,000
974	SAQIB	Apr-22	130,000
975	SAQIB	May-22	130,000
976	SAQIB	Jun-22	130,000
Sheet1

 

[KRice]

Welcome to the MrExcel board! Here is one approach. This formula is pulled down and across the results table until blanks are produced, indicating no more matches are found:

Do not Change of FY23 Operating Budget Planning_received 20220522_rev2_rec 20220731_v2.xlsx
	H	I	J
953	SOURCE DATA
954	EMPLOYEE NAME	SALARY MONTH	SALARY
955	SAQIB	Jan-22	120000
956	SAQIB	Feb-20	120000
957	SAQIB	Mar-22	120000
958	RAZA	Jan-22	110000
959	RAZA	Feb-22	110000
960	RAZA	Mar-22	110000
961	SAQIB	Apr-22	130000
962	SAQIB	May-22	130000
963	SAQIB	Jun-22	130000
964	RAZA	Apr-22	120000
965	RAZA	May-22	120000
966	RAZA	Jun-22	120000
967
968
969	RESULT
970	ENTER NAME	RAZA
971	RAZA	Jan-22	110000
972	RAZA	Feb-22	110000
973	RAZA	Mar-22	110000
974	RAZA	Apr-22	120000
975	RAZA	May-22	120000
976	RAZA	Jun-22	120000
977
978
979
980
Sheet2

Cell Formulas
Range	Formula
H971:J980	H971	=IFERROR(INDEX($H$955:$J$966,AGGREGATE(15,6,(ROW($H$955:$H$966)-ROW($H$955)+1)/($H$955:$H$966=$I$970),ROWS($H$971:$H971)),COLUMNS($H:H)),"")

 

[SAQIB_4414]

Welcome to the MrExcel board! Here is one approach. This formula is pulled down and across the results table until blanks are produced, indicating no more matches are found:

Do not Change of FY23 Operating Budget Planning_received 20220522_rev2_rec 20220731_v2.xlsx
	H	I	J
953	SOURCE DATA
954	EMPLOYEE NAME	SALARY MONTH	SALARY
955	SAQIB	Jan-22	120000
956	SAQIB	Feb-20	120000
957	SAQIB	Mar-22	120000
958	RAZA	Jan-22	110000
959	RAZA	Feb-22	110000
960	RAZA	Mar-22	110000
961	SAQIB	Apr-22	130000
962	SAQIB	May-22	130000
963	SAQIB	Jun-22	130000
964	RAZA	Apr-22	120000
965	RAZA	May-22	120000
966	RAZA	Jun-22	120000
967
968
969	RESULT
970	ENTER NAME	RAZA
971	RAZA	Jan-22	110000
972	RAZA	Feb-22	110000
973	RAZA	Mar-22	110000
974	RAZA	Apr-22	120000
975	RAZA	May-22	120000
976	RAZA	Jun-22	120000
977
978
979
980
Sheet2

Cell Formulas
Range	Formula
H971:J980	H971	=IFERROR(INDEX($H$955:$J$966,AGGREGATE(15,6,(ROW($H$955:$H$966)-ROW($H$955)+1)/($H$955:$H$966=$I$970),ROWS($H$971:$H971)),COLUMNS($H:H)),"")

♡ ♥❤♡ ♥❤

it's perfect ........................ thankssssssss

 

[KRice]

You're welcome...I'm happy to help.