0

I have student data listed across multiple columns.

Column A of the data contains student names.

While each row is specific to a single student named in column A, a single student may have multiple rows of data.

For example, StudentA's name may be listed 8 times, StudentB's 12 times, StudentC's 4 times, and so on.

I want to type a student's name in F3, and return the data in A:B where the value in column A matches F3.

This is my current formula:

=IFNA(INDIRECT("A"&MATCH(F3,A:A,0)&":E"&MATCH(F3,A:A,0)-1+MATCH("MYA",INDIRECT("A"&MATCH(F3,A:A,0)&":A"),0)),"Record not found")

In this case, I was searching for data for the student MYA. I would like it so that I can filter the list for a student without having to hardcode the student's name in the formula as I did above.

Sample Data

A B                                               
1 FirstName TestDate
2 BLAINE 04/17/2021
3 BLAINE 04/17/2021
4 BLAINE 04/17/2021
5 BLAINE 04/17/2021
6 MYA 03/01/2022
7 MYA 03/01/2022
8 MYA 03/01/2022
9 MYA 03/01/2022
10 IAN 02/23/2021
11 IAN 02/23/2021
12 IAN 02/23/2021
13 IAN 02/23/2021
14 IAN 02/23/2021

Sample Results

A B C D E F G                        
1 FirstName TestDate
2 BLAINE 04/17/2021 Lookup
3 BLAINE 04/17/2021 MYA
4 BLAINE 04/17/2021 Results
5 BLAINE 04/17/2021 MYA 3/1/2022
6 MYA 03/01/2022 MYA 3/1/2022
7 MYA 03/01/2022 MYA 3/1/2022
8 MYA 03/01/2022 MYA 3/1/2022 9 03/01/2022
10 IAN 02/23/2021
11 IAN 02/23/2021
12 IAN 02/23/2021
13 IAN 02/23/2021
14 IAN 02/23/2021

Link to my demo spreadsheet which you are welcome to edit

0

1 Answer 1

1

Use filter(), like this:

=filter(A2:E, A2:A = F3)

See the new Solution sheet in your sample spreadsheet. There is a drop-down list in cell F3.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.