Forum Discussion
INDEX-MATCH multiple results based on one cell, to fill horizontally in same row
Hi all,
First time posting here. (I have Excel 2019.) I've been experimenting with INDEX-MATCH function combinations over the last year or so, and I'm trying to create a more complicated formula that will automatically fill in as an array based on static data that is reuploaded into a different "Data Pull" sheet.
Below, I have included a screenshot of a workbook that I've been building to create an automated attendance database, including four distinct sheets:
- FA22 Students SL Absences
- This is a mix between formulas that utilize "NSGP_Pull" data and static data based on "All Students..."
- Basic Attendance Report
- Ignore this for this task
- NSGP_Pull
- Static data on student absences that is pulled from a database and then is copied and pasted into this sheet
- If a student has more than one absence, it is written in a new row below the first. It continues to write new absences below the name perpetually
- All Students in SL - Argos
- Static data pull that feeds into "FA22 Students..."
What I'm trying to do is use the static absence data pull in "NSGP_Pull" to create automated formulas in the first sheet "FA22..." so that I can see in one place how many absences a student has (formula in column D), when that absence was (columns J:M), and the attached note (if it exists) to that absence (columns N:Q).
Some students have multiple absences, and I want to be able to automatically populate the columns J:Q with data associated with their absences.
I've crafted an INDEX-MATCH formula beginning in J2 of "FA22..." which successfully references data in "FA22..." and "NSGP_Pull" to find a value. This formula indexes the date of absence in "NSGP_Pull" that is associated with the unique email identifier 'NSGP_Pull'E and matches it with column C in "FA22..." The formula also works to fill in multiple results when auto-filled down. HOWEVER, I want it to autofill horizontally with multiple results of dates of absences - I just am not yet skilled enough to figure out how to manipulate the formula to do that.
Could you please help me find a solution to automating this workbook, or perhaps advise me on a better formula to use to accomplish what I'm aiming to do? Please let me know if I can provide more information.
https://postimg.cc/TKM5sbwq
https://postimg.cc/fkbXFFnw
Thanks!
Blake
=IFERROR(INDEX(NSGP_Pull!$D$3:$D$425, SMALL(IF((NSGP_Pull!$E$3:$E$425=$C2) * ( NSGP_Pull!$F$3:$F$425=$F2), ROW(NSGP_Pull!$D$3:$D$425)-MIN(ROW(NSGP_Pull!$D$3:$D$425))+1), COLUMNS($C$2:C2))),"")
This works in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.
3 Replies
byohojcu so, I figured out this problem by adding a COLUMNS function at the end of the statement, and introducing a SMALL IF with ROW functions.
Current formula:
{=IFERROR(INDEX(NSGP_Pull!$D$3:$D$425, SMALL(IF(NSGP_Pull!$E$3:$E$425=$C2, ROW(NSGP_Pull!$D$3:$D$425)-MIN(ROW(NSGP_Pull!$D$3:$D$425))+1), COLUMNS($C$2:C2))),"")}
Now, I'm stuck because I realize that I actually want to INDEX based on 2 conditions for an IF statement, and when I attempt to use AND, it doesn't allow me to autofill the formula/array as such.
For example, I want this:
{=IFERROR(INDEX(NSGP_Pull!$D$3:$D$425, SMALL(IF(AND(NSGP_Pull!$E$3:$E$425=$C2, NSGP_Pull!$F$3:$F$425=$F2 ROW(NSGP_Pull!$D$3:$D$425)-MIN(ROW(NSGP_Pull!$D$3:$D$425))+1), COLUMNS($C$2:C2))),"")}
Or, I want the formula to check that BOTH $C2 and $F2 match with their ranges in sheet NSGP_Pull before giving me a result.
Can anyone assist?
Thanks!
-Blake
=IFERROR(INDEX(NSGP_Pull!$D$3:$D$425, SMALL(IF((NSGP_Pull!$E$3:$E$425=$C2) * ( NSGP_Pull!$F$3:$F$425=$F2), ROW(NSGP_Pull!$D$3:$D$425)-MIN(ROW(NSGP_Pull!$D$3:$D$425))+1), COLUMNS($C$2:C2))),"")
This works in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.
- Amazing, thank you! I am slowly starting to better understand the numerical values behind the formulas, so multiplying by the other value was so new to me. Solved -- so grateful for your help!
