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 |