INDEX-MATCH multiple results based on one cell, to fill horizontally in same row

[byohojcu]

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.

Screenshot of Workbook: https://postimg.cc/TKM5sbwq

Screenshot of NSGP_Pull sheet: https://postimg.cc/fkbXFFnw 

Thanks!

Blake

[#2]

@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

[#3 · Accepted solution]

@byohojcu 

=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.

[#4]

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!